overview.knit

Introduction

This project was completed as part of a paid internship with the International Water Security Network and was the dissertation project for an MSc in Data Science at the University of the West of England.

The project was written in the R programming language and utilises the shiny library (Chang et al., 2022) to create an interactive web application to provide users with an accessible interface to execute R code on the application backend.

All work was completed independently over a development life cycle of approximately 14 weeks.

Project Aims

The purpose of the project was to create a tool that could enable non-technical users to independently access and utilise raw data.

End users include professionals from the humanitarian sector currently working in response to the refugee crisis in Bangladesh to ensure affected communities receive adequate access to water, sanitation and hygiene facilities.

The tool I have created provides non-technical users with a platform that enables them to independently extract insight from data through a range of analytical and visualisation tools. Automatic data cleansing and an easy to use graphical interface removes the skills barrier that may prevent sector professionals from utilising data to enhance the effectiveness of aid initiatives in the region.

Project Pitch Video

Application Summary

Feature Walkthrough

Live Demo

Click thumbnail to view a live demo version of the application.

Note: A sample dataset is pre-loaded for demonstration purposes. File import and password entry widgets are retained for illustration only.

Code Repository

All code is available via my GitHub repository.
Functionning sample data for application use is located in the /data folder.

Data, Cleansing & Processing

Data

Socioeconomic Survey

In January 2022, a consortium of specialist practitioners and researchers from the United Nations High Council for Refugees (UNHCR), Oxfam, Ground Water Relief (GWR), Asian University for Women (AUW), and University of the West of England (UWE) collaborated to undertake a detailed socioeconomic survey of water usage in the Teknaf district of Bangladesh.

The survey gathered data from 462 members of the refugee and host communities.

Survey Questions

Survey questionnaire was composed of the following question categories:

questions <- read.csv("../data/questions_table.csv", header =TRUE)
colnames(questions) <- c("Variable Code", "Question Text")
# general
general <- questions[c(1:9),]
# sociodemographic
sd <- questions[grepl("sd", questions[,1]),]
# work
work <- questions[questions$`Variable Code` %in% c("w1_1","w2","w3","w4","w5","w5","w6","w7", "w8"),]
# perceptions
perc <- questions[questions$`Variable Code` %in% c("p1","p2","p3","p4","p5"),]
# water insecurity
hw <- questions[grepl("hw", questions[,1]),]
# water access & utility
wat <- questions[grepl("wat", questions[,1]),]
# livelihood activities
liv <- questions[grepl("liv", questions[,1]),]
# paying for water
pay <- questions[grepl("pay", questions[,1]),]
#wellbeing
wb <- questions[grepl("wb", questions[,1]),]

Sociodemographic

kable(sd, row.names = F) %>%
  kable_styling("striped", "condensed", full_width = FALSE) %>%
  scroll_box(width = "100%", height = "400px")

Variable Code	Question Text
sd1	Head Of Hh
sd2	Relationship Status
sd3	Age
sd4	Household: Responsible To Get Water
sd5_	Household: Ensures Sufficient Water
sd6_	Household: Children Under 18
sd7	Household: Adult Members
sd8	Household: Elderly Members
sd9	Left Myanmar
sd11	Financial Assistance
sd11_1	Financial Assistance: Amount
sd11_2	Financial Assistance: Source
sd11_3	Financial Assistance: Usage
sd12	Highest Level Of Education
sd13	House: Type
sd14	House: No. of Rooms
sd15	House: Has a Garden
sd16	House: Ownership
sd17	House: Electricity Supply
sd18	House: Piped Water Supply
sd19	House: Sewerage Connection
sd20	Primary Fuel Source
sd21	Rate Community: Socioeconomic Standing
sd22	Rate Community: Water Situation

Work

kable(work, row.names = F) %>%
  kable_styling("striped", "condensed", full_width = FALSE) %>%
  scroll_box(width = "100%")

Variable Code	Question Text
w1_1	Income Source: Employment In Government
w2	Income Source: Employment In Private Sector
w3	Income Source: Casual Labour
w4	Income Source: Own Business
w5	Income Source: Farming
w6	Income Source: From Family Relative
w7	Difficult To Obtain Sufficient Income
w8	Experienced Problems & Solutions

Perceptions

kable(perc, row.names = F) %>%
  kable_styling("striped", "condensed", full_width = FALSE) %>%
  scroll_box(width = "100%")

Variable Code	Question Text
p1	Water&Sanitation Needs Being Met
p2	Women & Men: Equal Responsibility for Sanitation
p3	Women & Men: Equal Awareness of Feedback Processes
p4	Women & Men: Feedback Equally Valued
p5	Women & Men: Equal Awareness of Sanitation Rights

Water Insecurity

kable(hw, row.names = F) %>%
  kable_styling("striped", "condensed", full_width = FALSE) %>%
  scroll_box(width = "100%", height = "400px")

Variable Code	Question Text
hw	Calculated HWISE Score
hw1	Worry About Water Supply
hw2	Supply Interruptions
hw2a	Supply Interruptions: Expected or Unexpected
hw3	Unable to do Laundry Due to Water Situation
hw4	Schedule Change Due to Water Situation
hw5	Change What Was Eaten Due to Water Situation
hw6	Unable to Wash Hands After Dirty Activity
hw7	Unable to Wash Body Due to Water Situation
hw8	Not Enough Water to Drink
hw9	Felt Anger About Water Situation
hw10	Gone to Sleep Thirsty
hw11	No Useable or Drinkable Water
hw12	Felt Shame About Water Situation
hw13	Asked to Borrow Water

Access & Utility

kable(wat, row.names = F) %>%
  kable_styling("striped", "condensed", full_width = FALSE) %>%
  scroll_box(width = "100%", height = "400px")

Variable Code	Question Text
wat1	Drinking Water: Primary Source
wat2	Drinking Water: Secondary Source
wat3	Non-drinking Water: Primary Source
wat4	Drinking Water: Time to Source (mins)
wat5	Drinking Water: No.of Trips
wat5a	Drinking Water: Collection Time Per Week (mins)
wat5b	Drinking Water: Collection Time Per Week (grouped)
wat6	Injured While Fetching Water
wat7	Problem With Water Quality
wat8	The Water Quality Problem
wat9	Non-drinking Water: Time to Source (mins)
wat10	Non-drinking Water: No.of Trips
wat11	Treated Water To Make It Safer
wat12_	Drinking Water Treatment
wat12_1	Drinking Water Treatment: Boil
wat12_2	Drinking Water Treatment: Filter
wat12_3	Drinking Water Treatment: Add Chemicals Chlorine Tablets
wat12_4	Drinking Water Treatment: Other Specify
wat12_5	Drinking Water Treatment: Do Nothing
wat12_6_	Drinking Water Treatment: Multiple Methods
wat14	Kolosh
wat14_1	Buckets
wat14_2	Jerry Cans
wat14_3	Other
wat15_	Fewest Water Problems
wat15_1	Fewest Water Problems: January
wat15_2	Fewest Water Problems: February
wat15_3	Fewest Water Problems: March
wat15_4	Fewest Water Problems: April
wat15_5	Fewest Water Problems: May
wat15_6	Fewest Water Problems: June
wat15_7	Fewest Water Problems: July
wat15_8	Fewest Water Problems: August
wat15_9	Fewest Water Problems: September
wat15_10	Fewest Water Problems: October
wat15_11	Fewest Water Problems: November
wat15_12	Fewest Water Problems: December
wat16	Main Cause Of Problems With Water
wat17	What Do You Do When You Dont Have Enough Water

Livelihood

kable(liv, row.names = F) %>%
  kable_styling("striped", "condensed", full_width = FALSE) %>%
  scroll_box(width = "100%", height = "400px")

Variable Code	Question Text
liv1_	Income Generation
liv1_1	Income Generation: Crop Production
liv1_2	Income Generation: Betel Nut Leaf Production
liv1_3	Income Generation: Livestock Production
liv1_4	Income Generation: Salt Production
liv1_5	Income Generation: Sea Fishing
liv1_6	Income Generation: Aquaculture Shrimp Farming
liv1_7	Income Generation: Aquaculture Fish Farming
liv1_8	Income Generation: Other
liv1_9	Income Generation: None Of Above
liv1_10_	Income Generation: Multiple Activities
liv2	Do You Own Businesses
liv3	Income Generation: Primary Water Source
liv4_	Income Generation: Problematic Water Quantity or Quality
liv5	Please Describe Problem
liv6	What You Did When You Experienced These Problems

Paying for Water

kable(pay, row.names = F) %>%
  kable_styling("striped", "condensed", full_width = FALSE) %>%
  scroll_box(width = "100%")

Variable Code	Question Text
pay1	Important To Pay To Install Your Own Water System
pay2	Important To Pay For Regular Costs
pay3	Fee Structure For Use Of Main System

Wellbeing

kable(wb, row.names = F) %>%
  kable_styling("striped", "condensed", full_width = FALSE) %>%
  scroll_box(width = "100%", height = "400px")

Variable Code	Question Text
wb1	How Do You Feel About Your Life
wb2	Change Would Make Biggest Positive Difference
wb3	General Health
wb4	No. Days Poor Physical Health
wb5	No. Days Poor Mental Health
wb6	No. Days Health Prevented Normal Activities
wb7	Feel Unable to Control Important Things
wb8	Feel Confident in Ability to Control Problems
wb9	Feel Things Going Your Way
wb10	Feel Difficulties Could Not Be Overcome

Cleansing & Processing

All data cleansing and processing is performed automatically upon successful import of the survey dataset into the application. No interaction or experience in data processing is required from the end user.

The dataset is provided as a password protected, single sheet .xlsx file that is imported using the excel.link library (Demin, 2021).

Data Structures

Four data objects are created in total:

Data Objects

Raw Data

An initial raw_data object is created to store the entire dataset. Only minimal transformations to support indexing are performed:

All characters converted to lower case.
Column names set as variable codes.
Digits appended to ensure column names are unique.

# import data
raw_data <- try(xl.read.file("../data/demo_data.xlsx",
                           header = FALSE,
                           password =  "password"))

# Change all chars to lowercase
raw_data <- data.frame(sapply(raw_data, tolower))

# If row 1 (variable code) is blank, fill with row 2 (filed name)
for (i in 1: ncol(raw_data)) {
      ifelse(is.na(raw_data[1, i]), raw_data[1, i] <- raw_data[2, i ], raw_data[1, i])
    }
    
# Set row 1 as colnames and then remove row 1
colnames(raw_data) <- raw_data[1, ]
raw_data <- raw_data[-1, ]

# Append digits to make colnames unique
colnames(raw_data) <- make.unique(colnames(raw_data), sep = "_")

kable(head(raw_data)) %>%
  kable_styling("striped", "condensed", full_width = FALSE) %>%
  scroll_box(width = "100%", height = "400px")

	submissiondate	starttime	endtime	deviceid	subscriberid	simid	devicephonenum	username	duration	caseid	informed_consent	informed_consent_1	consent	consent_for_taking_photos	interviewer	date	country	location	union	ward	village	camp	block	gps-latitude	gps-longitude	gps-altitude	gps-accuracy	sex	unique_id	sd1	sd2	sd3	sd4	sd5	sd5_1	sd5_2	sd5_3	sd5_4	sd5_5	sd5_6	sd6	sd6_1	sd7	sd8	sd9	sd11	sd11_1	sd11_2	sd11_3	sd12	sd13	sd13_1	sd14	sd15	sd16	sd16_1	sd17	sd18	sd19	sd20	sd20_1	sd21	sd22	w1	w1_1	w2	w3	w4	w5	w6	w7	w8	p1	p2	p3	p4	p5	hw1	hw2	hw2a	hw3	hw4	hw5	hw6	hw7	hw8	hw9	hw10	hw11	hw12	hw13	wat1	wat2	wat3	wat4	wat5	wat6	wat7	wat8	wat9	wat10	wat11	wat12	wat12_1	wat12_2	wat12_3	wat12_4	wat12_5	wat12_6	wat13	wat13_1	wat13_2	wat14	wat14_1	wat14_2	wat14_3	wat14_4	wat15	wat15_1	wat15_2	wat15_3	wat15_4	wat15_5	wat15_6	wat15_7	wat15_8	wat15_9	wat15_10	wat15_11	wat15_12	wat16	wat17	liv1	liv1_1	liv1_2	liv1_3	liv1_4	liv1_5	liv1_6	liv1_7	liv1_8	liv1_9	liv2	liv3	liv4	liv4_1	liv4_2	liv4_3	liv5	liv6	pay1	pay2	pay3	wb1	wb2	wb3	wb4	wb5	wb6	wb7	wb8	wb9	wb10	dq2	dq2_1	dq2_2	dq2_3	dq2_4	dq2_5	dq2_6	dq3	dq4	dq5	instanceid	formdef_version	key	review_quality	review_comments	review_corrections
2	submissiondate	starttime	endtime	deviceid	subscriberid	simid	devicephonenum	username	duration	caseid	informed_consent	informed_consent	consent	consent_for_taking_photos	interviewer	date	country	location	union	ward	village	camp	block	gps-latitude	gps-longitude	gps-altitude	gps-accuracy	sex	unique_id	head_of_hh	current_status	age	responsible_to_get_water	make_sure_there_is_enough_water_in_the_house	make_sure_there_is_enough_water_in_the_house_self	make_sure_there_is_enough_water_in_the_house_spouse	make_sure_there_is_enough_water_in_the_house_boy_children	make_sure_there_is_enough_water_in_the_house_girl_children	make_sure_there_is_enough_water_in_the_house_other_family_members	make_sure_there_is_enough_water_in_the_house_shared_responsibility	under_18_years_male	under_18_years_female	above_18_years	elderly	left_mayanmar	financial_assistance_remittances	receiving_assistance_remittances	assistance_from_what_source	assistance_usage	highest_level_of_education	physical_type_of_housing	please_specify	how_many_rooms	does_the_house_have_a_garden	ownership_of_the_house	specify-	have_electricity_supply	piped_water_supply	connection_to_sewerage	primary_fuel_source_for_heating-cooking	describe	place_yourself_on_this_ladder	place_yourself_on_this_ladder_2	income_source_past_12_months	income_source_past_12_months_employment_in_government	income_source_past_12_months_employment_in_private_sector_ngo	income_source_past_12_months_casual_labour_food_for_work	income_source_past_12_months_own_business	income_source_past_12_months_farming_agriculture_and_animal	income_source_past_12_months_remittances_from_family_relative	difficult_to_obtain_sufficient_income	what_problems_and_what_you_did	water_and_sanitation_needs_are_being_met	women_men_share_equal_responsibility	women_men_are_equally_aware_of_how_to_provide_feedback	feedback_from_women_men_about_local_water_and_sanitation_management_is_equally_valued.	women_and_men_are_equally_aware_of_their_water_sanitation_rights	last_four_weeks_not_having_enough_water	last_four_weeks_main_water_source_been_interrupted	supply_was_interrupted_were_these_expected_or_unexpected	how_frequently_has_there_not_been_enough_water_to_wash_clothes	change_schedules_or_plans_due_to_problems_with_your_water_situation	had_to_change_what_was_being_eaten_because	had_to_go_without_washing_hands_after_dirty_activities	had_to_go_without_washing_their_body	has_there_not_been_as_much_water_to_drink	you_or_anyone_in_household_feel_angry_about_your_water_situation	have_you_or_anyone_in_your_household_gone_to_sleep_thirsty	has_there_been_no_usable_or_drinkable_water	problems_with_water_caused_you_or_household_member_to_feel_ashamed	have_you_or_household_member_asked_to_borrow_water	primary_preferred_source_of_drinking_water	secondary_second_choice_source_of_drinking_water	primary_source_of_water_for_other_purposes_non_drinking_water	how_long_does_it_take_to_go_to_the_water_source	how_many_trips_to_collect_drinking_water	injured_while_fetching_water	you_had_problem_with_water_quality	the_water_quality_problem	how_long_does_it_to_go_to_the_water_source	how_many_trips_to_collect_water_for_non_drinking_purposes	treated_water_to_make_it_safer	primary_way_household_treats_drinking_water	primary_way_household_treats_drinking_water_boil	primary_way_household_treats_drinking_water_filter	primary_way_household_treats_drinking_water_add_chemicals_chlorine_tablets	primary_way_household_treats_drinking_water_other_specify	primary_way_household_treats_drinking_water_do_nothing	specifications	okay_for_us_to_take_photos	pictures	pictures_copy	kolosh	buckets	jerry_cans	other	specify	your_household_experience_the_fewest_water_problems	your_household_experience_the_fewest_water_problems_january	your_household_experience_the_fewest_water_problems_february	your_household_experience_the_fewest_water_problems_march	your_household_experience_the_fewest_water_problems_april	your_household_experience_the_fewest_water_problems_may	your_household_experience_the_fewest_water_problems_june	your_household_experience_the_fewest_water_problems_july	your_household_experience_the_fewest_water_problems__	your_household_experience_the_fewest_water_problems_september	your_household_experience_the_fewest_water_problems_october	your_household_experience_the_fewest_water_problems_november	your_household_experience_the_fewest_water_problems_december	main_cause_of_problems_with_water	what_do_you_do_when_you_dont_have_enough_water	activities_are_you_engaged_in_for_household_food_production_or_income_generation	activities_are_you_engaged_in_for_household_food_production_or_income_generation_crop_production	activities_are_you_engaged_in_for_household_food_production_or_income_generation_betel_nut_leaf_production	activities_are_you_engaged_in_for_household_food_production_or_income_generation_livestock_production	activities_are_you_engaged_in_for_household_food_production_or_income_generation_salt_production	activities_are_you_engaged_in_for_household_food_production_or_income_generation_sea_fishing	activities_are_you_engaged_in_for_household_food_production_or_income_generation_aquaculture_shrimp_farming	activities_are_you_engaged_in_for_household_food_production_or_income_generation_aquaculture_fish_farming	activities_are_you_engaged_in_for_household_food_production_or_income_generation_other	activities_are_you_engaged_in_for_household_food_production_or_income_generation_none_of_the_above	do_you_own_the_businesses	primary_source_of_water_for_these_activities	experienced_problems_with_the_quantity_and_or_quality	experienced_problems_with_the_quantity_and_or_quality_yes_quantity	experienced_problems_with_the_quantity_and_or_quality_yes_quality	experienced_problems_with_the_quantity_and_or_quality_no	please_describe_the_problem	what_you_did_when_you_experienced_these_problems	important_to_pay_to_install_your_own_water_system	important_do_you_think_it_is_for_you_to_pay_for_regular_costs	fee_structure_for_use_of_the_main_system	how_do_you_feel_about_your_life	change_would_make_the_biggest_positive_difference	in_general_that_your_health_is	how_many_days_during_past_30_days_would_you_say_that_your_physical_health_was_poor	how_many_days_during_the_past_30_days_was_your_mental_health_poor	how_many_days_during_the_past_30_days_did_poor_physical_or_mental_health_keep_you_from_doing_your_usual_activities	how_often_have_you_felt_that_you_were_unable_to_control_the_important_things	how_often_have_you_felt_confident_about_your_ability_to_handle	how_often_have_you_felt_that_things_were_going_your_way	how_often_have_you_felt_difficulties_were_piling_up_so_high_that_you_could_not_overcome	did_the_respondent_show_any_of_the_following	did_the_respondent_show_any_of_the_following_mistrust_of_you_or_the_study	did_the_respondent_show_any_of_the_following_dishonesty_lying	did_the_respondent_show_any_of_the_following_fear_of_you_or_the_study	did_the_respondent_show_any_of_the_following_hostility_anger_resentment	did_the_respondent_show_any_of_the_following_evasion_avoid_answering	did_the_respondent_show_any_of_the_following_none_of_the_above	any_interruptions_or_distractions	overall_assessment_of_the_quality_of_the_data	interviewers_comments	instanceid	formdef_version	key	review_quality	review_comments	review_corrections
3	4/17/2022 15:41	4/17/2022 12:20	4/17/2022 13:10	8.64e+14	4.7e+14	8.99e+19	NA	mealenumerator	2604	NA	1	1	1	1	faroque	2022-04-17	bangladesh	host	whykong	6	jimonkhali	NA	NA	21.0593875	92.2269164	-39.2	5	male	149	myself	married	40	my_spouse	self spouse boy_children	1	1	1	0	0	0	1	1	2	2	NA	0	NA	NA	NA	primary_school	processed_wood	NA	3	0	owned	NA	1	0	0	wood	NA	5	4	own_business	0	0	0	1	0	0	1	business	true	true	true	true	true	1-2_times	1-2_times	unexpected	0_times	0_times	0_times	0_times	0_times	1-2_times	0_times	0_times	0_times	0_times	0_times	borehole/tubewell	borehole/tubewell	borehole/tubewell	2	3	0	1	salty water, iron	2	3	1	filter	0	1	0	0	0	NA	1	https://oxfamrohingyar.surveycto.com/view/submission-attachment/1650178248261.jpg?uuid=uuid%3ab93e131b-e030-4f15-b706-876d53e06d7f	https://oxfamrohingyar.surveycto.com/view/submission-attachment/1650178278137.jpg?uuid=uuid%3ab93e131b-e030-4f15-b706-876d53e06d7f	3	4	1	1 drum	NA	september october	0	0	0	0	0	0	0	0	1	1	0	0	the water is salty	water needs to be collected from remote area	none_of_the_above	0	0	0	0	0	0	0	0	1	yes_i_own_the_businesses	borehole/tubewell	yes_quality	0	1	0	because the water is salty, the shop has to collect water from far away	1 / pure water is not easy to drink because water is salty. 2 / there is a problem with cooking for water 3 / water is not easily available for business.	somewhat_important	not_important_at_all	it_is_free	somewhat_dissatisfied	safe_water_services	very_good	3	2	2	fairly_often	fairly_often	fairly_often	fairly_often	none_of_the_above	0	0	0	0	0	1	no	excellent	thank you for taking our information the people of the area will be much happier if they can be provided pure water.	uuid:b93e131b-e030-4f15-b706-876d53e06d7f	2204171148	uuid:b93e131b-e030-4f15-b706-876d53e06d7f	good	msiddique@oxfam.org.uk (may 4, 2022 4:06:57 pm): [ submission un-approved. ] npaul@oxfam.org.uk (may 8, 2022 11:23:41 am): [ submission approved. classified as unknown.]	npaul@oxfam.org.uk (may 8, 2022 11:23:01 am): assistance_from_what_source, the_water_quality_problem, main_cause_of_problems_with_water, what_do_you_do_when_you_dont_have_enough_water, please_describe_the_problem, what_you_did_when_you_experienced_these_problems, interviewers_comments
4	4/17/2022 15:42	4/17/2022 12:32	4/17/2022 13:13	714132bb35b619f1	NA	NA	NA	mealenumerator	2457	NA	1	1	1	1	ashif	2022-04-17	bangladesh	host	whykong	8	kharangkhali	NA	NA	21.04305	92.2335683	-51.8	4.2	female	300	my_spouse	married	30	my_spouse	self spouse other_family_members	1	1	0	0	1	0	1	2	2	0	NA	0	NA	NA	NA	secondary_school	wood/canvas/plastic	NA	2	0	owned	NA	1	0	0	gas_bottles	NA	2	3	own_business	0	0	0	1	0	0	1	covid-19	false	true	true	true	true	1-2_times	11-20_times	announced/scheduled	11-20_times	11-20_times	3-10_times	11-20_times	11-20_times	3-10_times	11-20_times	3-10_times	3-10_times	11-20_times	11-20_times	stand_pipe	stand_pipe	surface_water_pond_river_lake	120	2	0	1	the taste becomes salty, the color becomes red.	90	3	0	other_specify	0	0	0	1	0	no treatment is required.	1	https://oxfamrohingyar.surveycto.com/view/submission-attachment/1650178612580.jpg?uuid=uuid%3ac76ee428-6b85-4a1c-ae36-da570a7c8ac6	https://oxfamrohingyar.surveycto.com/view/submission-attachment/1650178650383.jpg?uuid=uuid%3ac76ee428-6b85-4a1c-ae36-da570a7c8ac6	4	4	6	drum-01	NA	march april	0	0	1	1	0	0	0	0	0	0	0	0	the water level goes down.	i will make arrangements to collect water.	none_of_the_above	0	0	0	0	0	0	0	0	1	no_i_work_for_somebody_else	other	yes_quantity yes_quality	1	1	0	in the dry season the water decreases and the water becomes salty and red.	need to collect water from others villages	very_important	very_important	fixed_cost_per_month	somewhat_satisfied	safe_water_services	fair	4	5	5	fairly_often	fairly_often	fairly_often	fairly_often	none_of_the_above	0	0	0	0	0	1	no	excellent	no	uuid:c76ee428-6b85-4a1c-ae36-da570a7c8ac6	2204171148	uuid:c76ee428-6b85-4a1c-ae36-da570a7c8ac6	good	msiddique@oxfam.org.uk (may 4, 2022 4:14:25 pm): [ submission un-approved. ] npaul@oxfam.org.uk (may 8, 2022 11:27:31 am): [ submission approved. classified as unknown.]	msiddique@oxfam.org.uk (may 4, 2022 4:27:15 pm): what_you_did_when_you_experienced_these_problems, please_describe_the_problem, what_do_you_do_when_you_dont_have_enough_water, main_cause_of_problems_with_water, specifications, the_water_quality_problem
5	4/17/2022 15:42	4/17/2022 13:10	4/17/2022 13:48	8.64e+14	4.7e+14	8.99e+19	NA	mealenumerator	2062	NA	1	1	1	1	faroque	2022-04-17	bangladesh	host	whykong	6	jimonkhali	NA	NA	21.0597728	92.2273266	-41.6	3.9	female	278	my_spouse	married	50	my_spouse	self spouse other_family_members	1	1	0	0	1	0	0	1	3	2	2017	0	NA	NA	NA	primary_school	wood/canvas/plastic	NA	2	0	allocated/given_by_authorities	NA	0	0	0	gas_bottles	NA	4	8	own_business farming_agriculture_and_animal	0	0	0	1	1	0	1	i have to borrow	true	true	true	true	true	1-2_times	1-2_times	announced/scheduled	0_times	1-2_times	3-10_times	0_times	0_times	1-2_times	1-2_times	0_times	0_times	0_times	1-2_times	borehole/tubewell	borehole/tubewell	borehole/tubewell	3	5	0	1	water is a little bit solty	3	7	1	filter	0	1	0	0	0	NA	1	https://oxfamrohingyar.surveycto.com/view/submission-attachment/1650180645879.jpg?uuid=uuid%3abce33d6e-c3b3-4745-9853-94ff7c287234	https://oxfamrohingyar.surveycto.com/view/submission-attachment/1650180661220.jpg?uuid=uuid%3abce33d6e-c3b3-4745-9853-94ff7c287234	2	5	2	1 drum	NA	july august	0	0	0	0	0	0	1	1	0	0	0	0	water dries up due to overcrowding and overuse.	it is difficult to collect water from a distance.	none_of_the_above	0	0	0	0	0	0	0	0	1	yes_i_own_the_businesses	borehole/tubewell	yes_quality	0	1	0	it is very difficult to drink pure water because the water is salty.	water has to be collected and brought from far away.	not_important_at_all	not_important_at_all	it_is_free	somewhat_satisfied	better_health_services	very_good	3	2	2	fairly_often	almost_never	almost_never	fairly_often	none_of_the_above	0	0	0	0	0	1	no	excellent	thank you so much for collecting our information. we hope you will solve our problem of pure water.	uuid:bce33d6e-c3b3-4745-9853-94ff7c287234	2204171148	uuid:bce33d6e-c3b3-4745-9853-94ff7c287234	good	msiddique@oxfam.org.uk (may 4, 2022 5:04:07 pm): [ submission un-approved. ] npaul@oxfam.org.uk (may 8, 2022 11:29:01 am): [ submission approved. classified as unknown.]	msiddique@oxfam.org.uk (may 4, 2022 5:12:45 pm): interviewers_comments, what_you_did_when_you_experienced_these_problems, please_describe_the_problem, what_do_you_do_when_you_dont_have_enough_water, main_cause_of_problems_with_water, the_water_quality_problem, what_problems_and_what_you_did, assistance_usage, assistance_from_what_source
6	4/17/2022 15:42	4/17/2022 13:17	4/17/2022 13:42	714132bb35b619f1	NA	NA	NA	mealenumerator	1497	NA	1	1	1	1	ashif	2022-04-17	bangladesh	host	whykong	8	purbo moheskhalia para	NA	NA	21.0433602	92.2336067	-27.1	4.7	female	410	myself	widowed	57	myself	self boy_children girl_children other_family_members shared_responsibility	1	0	1	1	1	1	1	4	6	1	NA	0	NA	NA	NA	no_formal_education	concrete/brick	NA	4	1	owned	NA	1	1	0	wood	NA	4	4	employment_in_private_sector_ngo own_business	0	1	0	1	0	0	1	covid 19	false	true	true	true	true	11-20_times	11-20_times	announced/scheduled	3-10_times	3-10_times	3-10_times	3-10_times	3-10_times	3-10_times	3-10_times	1-2_times	1-2_times	3-10_times	11-20_times	stand_pipe	stand_pipe	stand_pipe	40	2	0	1	the water tastes salty and the water turns red.	40	2	0	other_specify	0	0	0	1	0	no treatment.	1	https://oxfamrohingyar.surveycto.com/view/submission-attachment/1650181128434.jpg?uuid=uuid%3a18171dc5-d2e5-46dc-a2fd-eb225e616508	https://oxfamrohingyar.surveycto.com/view/submission-attachment/1650181178229.jpg?uuid=uuid%3a18171dc5-d2e5-46dc-a2fd-eb225e616508	3	5	4	2 dram, 2 water pot	NA	march april	0	0	1	1	0	0	0	0	0	0	0	0	the water level goes down.	arranges water supply.	none_of_the_above	0	0	0	0	0	0	0	0	1	no_i_work_for_somebody_else	stand_pipe	yes_quantity yes_quality	1	1	0	the taste of water decreases and the color turns red.	i have collected water from the house next door.	very_important	very_important	vc_permonth_on_set_factors	somewhat_satisfied	safe_water_services	good	3	4	6	fairly_often	fairly_often	fairly_often	very_often	none_of_the_above	0	0	0	0	0	1	no	excellent	no	uuid:18171dc5-d2e5-46dc-a2fd-eb225e616508	2204171148	uuid:18171dc5-d2e5-46dc-a2fd-eb225e616508	good	msiddique@oxfam.org.uk (may 4, 2022 5:13:00 pm): [ submission un-approved. ] npaul@oxfam.org.uk (may 8, 2022 11:30:57 am): [ submission approved. classified as unknown.]	msiddique@oxfam.org.uk (may 4, 2022 5:16:51 pm): what_you_did_when_you_experienced_these_problems, please_describe_the_problem, what_do_you_do_when_you_dont_have_enough_water, main_cause_of_problems_with_water, other, specifications, the_water_quality_problem
7	4/17/2022 15:42	4/17/2022 13:43	4/17/2022 14:05	714132bb35b619f1	NA	NA	NA	mealenumerator	1352	NA	1	1	1	1	ashif	2022-04-17	bangladesh	host	whykong	8	purbo moheskhalia para	NA	NA	21.0433432	92.2337799	-48	4.9	female	247	myself	married	35	myself	self girl_children	1	0	0	1	0	0	3	1	3	2	NA	0	NA	NA	NA	no_formal_education	wood/canvas/plastic	NA	3	1	owned	NA	1	1	0	wood	NA	3	3	own_business	0	0	0	1	0	0	0	NA	false	true	true	true	true	3-10_times	3-10_times	announced/scheduled	3-10_times	3-10_times	3-10_times	3-10_times	3-10_times	1-2_times	3-10_times	1-2_times	3-10_times	11-20_times	11-20_times	stand_pipe	other	surface_water_pond_river_lake	30	2	0	1	the taste becomes salty and the color turns red.	120	4	0	other_specify	0	0	0	1	0	no treatment is given	1	https://oxfamrohingyar.surveycto.com/view/submission-attachment/1650182298246.jpg?uuid=uuid%3aa9bc0cc8-1ff4-4f25-bcd5-91465479d019	https://oxfamrohingyar.surveycto.com/view/submission-attachment/1650182353626.jpg?uuid=uuid%3aa9bc0cc8-1ff4-4f25-bcd5-91465479d019	10	10	0	one dram, 15 small bottol	NA	march april	0	0	1	1	0	0	0	0	0	0	0	0	going down the water level	i arrange water supply.	none_of_the_above	0	0	0	0	0	0	0	0	1	no_i_work_for_somebody_else	stand_pipe	yes_quantity yes_quality	1	1	0	the amount decreases and the water turns red.	i supply water from the next village.	very_important	very_important	fixed_cost_per_month	somewhat_satisfied	safe_water_services	fair	4	5	5	fairly_often	fairly_often	fairly_often	fairly_often	none_of_the_above	0	0	0	0	0	1	no	excellent	no	uuid:a9bc0cc8-1ff4-4f25-bcd5-91465479d019	2204171148	uuid:a9bc0cc8-1ff4-4f25-bcd5-91465479d019	good	msiddique@oxfam.org.uk (may 4, 2022 5:30:22 pm): [ submission un-approved. ] npaul@oxfam.org.uk (may 8, 2022 11:31:23 am): [ submission approved. classified as unknown.]	msiddique@oxfam.org.uk (may 4, 2022 5:34:39 pm): what_you_did_when_you_experienced_these_problems, please_describe_the_problem, what_do_you_do_when_you_dont_have_enough_water, main_cause_of_problems_with_water, other, specifications, the_water_quality_problem

Numeric Data

A num_data object is created for use in plotting and visualisation. All survey responses (except free text and factor data) are transformed to the numeric code as defined in the survey.

The following transformations are applied:

Variables of no use to application functionality are dropped.
Survey responses converted to numeric code.
Multiple choice questions encoded as expanded binary responses are collapsed.
Data classes corrected.
Various instances of inconsistent recording rectified (i.e. sd9: ‘Years since leaving Myanmar’ encoded variously as number of years as well as actual year).
Various new variables computed and added (i.e. Total HWISE score calculated from all HW response variables).

# Drop unneeded columns
num_data <- select(raw_data,
             "location",
             "union",
             "ward",
             "village",
             "camp",
             "block",
             "gps-latitude",
             "gps-longitude",
             "sex",
             starts_with(c("sd","w","p","hw","wat","liv","wb")),
             -c("sd5","sd13_1","sd16_1","sd20_1","w1","wat12",
                "wat12_6","wat13","wat13_1","wat13_2","wat14_4","wat15",
                "liv1","liv4")) %>%
  slice(-1)
    
# Replace text responses with numeric codes ----

## True / False
num_data[ , ] <- apply(num_data[ , ], 2, function(x) {
  x = replace(x, which(x=="true"),1)
  x = replace(x, which(x=="false"), 0)
})

## sd questions
num_data[, grepl("sd", names(num_data))] <- apply(num_data[,grepl("sd", names(num_data))], 2, function(x){
  x = replace(x, which(x=="myself"|x=="single"|x=="primary_school"|x=="concrete/brick"|x=="owned"|x=="wood"),1)
  x = replace(x, which(x=="my_spouse"|x=="divorced"|x=="secondary_school"|x=="processed_wood"|x=="rented"|x=="gas_bottles"),2)
  x = replace(x, which(x=="an_adult_child"|x=="adult_child"|x=="widowed"|x=="university_college"|x=="wood/canvas/plastic"|x=="allocated/given_by_authorities"),3)
  x = replace(x, which(x=="a_grandparent"|x=="married"|x=="no_formal_education"),4)
})
### "other" not consistently recorded
num_data[c("sd1","sd4","sd12","sd13")][num_data[c("sd1","sd4","sd12","sd13")] == "other"] <- 5
num_data["sd16"][num_data["sd16"] == "other"] <- 4
num_data["sd20"][num_data["sd20"] == "other"] <- 3

## hw questions
num_data[, grepl("hw", names(num_data))] <- apply(num_data[, grepl("hw",names(num_data))], 2, function(x){
  x = replace(x, which(x=="0_times"|x=="announced/scheduled"), 1)
  x = replace(x, which(x=="1-2_times"|x=="unexpected"), 2)
  x = replace(x, which(x=="3-10_times"), 3)
  x = replace(x, which(x=="11-20_times"), 4)
  x = replace(x, which(x=="more_than_20_times"), 5)
  x = replace(x, which(x=="not_applicable"), 88)
  x = replace(x, which(x=="dont know"), 99)
})

## wat questions
num_data[, grepl("wat", names(num_data))] <- apply(num_data[, grepl("wat",names(num_data))], 2, function(x){
  x = replace(x, which(x=="no"), 0)
  x = replace(x, which(x=="piped_water_to_dwelling"|x=="yes"), 1)
  x = replace(x, which(x=="stand_pipe"|x=="dont know"), 2)
  x = replace(x, which(x=="borehole/tubewell"), 3)
  x = replace(x, which(x=="protected_dug_well"), 4)
  x = replace(x, which(x=="unprotected_dug_well"), 5)
  x = replace(x, which(x=="protected_sping"), 6)
  x = replace(x, which(x=="unprotected_spring"), 7)
  x = replace(x, which(x=="rainwater_collection"), 8)
  x = replace(x, which(x=="small_water_vendor"), 9)
  x = replace(x, which(x=="tanker_truck"), 10)
  x = replace(x, which(x=="bottled_water"), 11)
  x = replace(x, which(x=="bagged_sachet_water"), 12)
  x = replace(x, which(x=="surface_water_pond_river_lake"), 13)
  x = replace(x, which(x=="other_person"), 14)
  x = replace(x, which(x=="other"), 15)
})

## liv questions
num_data[, grepl("liv",names(num_data))] <- apply(num_data[, grepl("liv",names(num_data))], 2, function(x){
  x = replace(x, which(x=="piped_water_to_dwelling"|x=="yes_i_own_the_businesses"), 1)
  x = replace(x, which(x=="stand_pipe"|x=="no_i_work_for_somebody_else"), 2)
  x = replace(x, which(x=="borehole/tubewell"), 3)
  x = replace(x, which(x=="protected_dug_well"), 4)
  x = replace(x, which(x=="unprotected_dug_well"), 5)
  x = replace(x, which(x=="protected_sping"), 6)
  x = replace(x, which(x=="unprotected_spring"), 7)
  x = replace(x, which(x=="rainwater_collection"), 8)
  x = replace(x, which(x=="small_water_vendor"), 9)
  x = replace(x, which(x=="tanker_truck"), 10)
  x = replace(x, which(x=="bottled_water"), 11)
  x = replace(x, which(x=="bagged_sachet_water"), 12)
  x = replace(x, which(x=="surface_water_pond_river_lake"), 13)
  x = replace(x, which(x=="other_person"), 14)
  x = replace(x, which(x=="other"), 15)
  x = replace(x, which(is.na(x)), 88)
  })

## pay questions
num_data[, grepl("pay", names(num_data))] <- apply(num_data[, grepl("pay",names(num_data))], 2, function(x){
  x=replace(x, which(x=="not_important_at_all"|x=="fixed_cost_per_month"),1)
  x=replace(x, which(x=="neither_imp_nor_unimportant"|x=="fixed_cost_per_amount_"),2)
  x=replace(x, which(x=="somewhat_important"|x=="vc_permonth_on_set_factors"),3)
  x=replace(x, which(x=="very_important"|x=="variable_cost_per_amount_or_use"),4)
  x=replace(x, which(x=="no_fixed_payment_is_made"),5)
  x=replace(x, which(x=="it_is_free"),6)
  x=replace(x, which(x=="dont_know"),7)
})

## wb questions
num_data[, grepl("wb", names(num_data))] <- apply(num_data[, grepl("wb",names(num_data))], 2, function(x){
  x = replace(x, which(x=="never"),0)
  x = replace(x, which(x=="very_dissatisfied"|x=="better_health_services"|x=="poor"|x=="almost_never"),1)
  x = replace(x, which(x=="somewhat_dissatisfied"|x=="safe_water_services"|x=="fair"|x=="sometimes"),2)
  x = replace(x, which(x=="somewhat_satisfied"|x=="access_to_education"|x=="good"|x=="fairly_often"),3)
  x = replace(x, which(x=="very_satisfied"|x=="better_communication_with_host_communities"|x=="very_good"|x=="very_often"),4)
  x = replace(x, which(x=="safe_return_to_myanmar"|x=="excellent"),5)
  x = replace(x, which(x=="other"),6)
})

# Define numeric variables ----
num_data <- num_data %>%
  mutate(across(-c("location",
                   "union",
                   "ward",
                   "village",
                   "camp",
                   "block",
                   "sex",
                   "sd11_2",
                   "sd11_3",
                   "w8",
                   "wat8",
                   "wat14_3",
                   "wat16",
                   "wat17",
                   "liv5",
                   "liv6"), as.numeric))
    
# Re-code for consistency ----

## Irregular 'ngo' recording
num_data["sd11_2"][num_data["sd11_2"] == "ngos"] <- "ngo"

## camp names
num_data$camp[grepl("nayapara", num_data$camp)] <- "Nayapara Refugee Camp"
num_data$camp <- sapply(num_data$camp, function(x){
  x = replace(x, which(x=="24"), "Camp 24")
  x = replace(x, which(x=="25"), "Camp 25")
  x = replace(x, which(x=="26"), "Camp 26")
  x = replace(x, which(x=="27"), "Camp 27")
})

## irregular sd9
for(i in 1:nrow(num_data)) {
  if ((!is.na(num_data[i,"sd9"])) & (num_data[i,"sd9"] < 1900)) {num_data[i,"sd9"] = 2022 - num_data[i,"sd9"]}
}
    
# Compute new variables ----

## Overall HWISE score
df_hw <- num_data %>%
  select(starts_with("hw"), -("hw2a"))

num_data$hw <- rowSums(pmin(as.matrix(df_hw - 1), 3))
rm(df_hw)

num_data <- num_data %>% 
  relocate(hw, .before = hw1)
    
## Time to collect per week
num_data <- num_data %>% 
  mutate(wat5a = (wat4 * wat5) * 7) %>%
  relocate(wat5a, .after = wat5)
## Grouped version
num_data$wat5b <- sapply(num_data$wat5a, function(x){
  x = replace(x, which(x<=100), 1)
  x = replace(x, which(x>100 & x<=200), 2)
  x = replace(x, which(x>200 & x<=300), 3)
  x = replace(x, which(x>300 & x<=400), 4)
  x = replace(x, which(x>400 & x<=500), 5)
  x = replace(x, which(x>500 & x<=600), 6)
  x = replace(x, which(x>600), 7)
})
num_data <- num_data %>% relocate(wat5b, .after = wat5a)
    
## Collapse boy/girl under 18 to single var
num_data <- num_data %>% 
  mutate(sd6_ = sd6 + sd6_1) %>%
  relocate(sd6_, .before = sd6) %>%
  select(-c(sd6, sd6_1))
    
# Collapse multiple choice binary questions ----
    
## sd5
# collapse to sd5_ * binaries dropped
num_data <- num_data %>%
  mutate(
    sd5_ = case_when(
      sd5_6 == 1 ~ 6,
      sd5_1 == 1 & sd5_2 == 0 & sd5_3 == 0 & sd5_4 == 0 & sd5_5 == 0 & sd5_6 ==0 ~ 1,
      sd5_1 == 0 & sd5_2 == 1 & sd5_3 == 0 & sd5_4 == 0 & sd5_5 == 0 & sd5_6 ==0 ~ 2,
      sd5_1 == 0 & sd5_2 == 0 & sd5_3 == 1 & sd5_4 == 0 & sd5_5 == 0 & sd5_6 ==0 ~ 3,
      sd5_1 == 0 & sd5_2 == 0 & sd5_3 == 0 & sd5_4 == 1 & sd5_5 == 0 & sd5_6 ==0 ~ 4,
      sd5_1 == 0 & sd5_2 == 0 & sd5_3 == 0 & sd5_4 == 0 & sd5_5 == 1 & sd5_6 ==0 ~ 5,
      TRUE ~ 6)
  ) %>%
  relocate(sd5_, .before = sd5_1) %>%
  select(-c(sd5_1, sd5_2, sd5_3, sd5_4, sd5_5, sd5_6))

## wat12
# collapse in sd12_ * binaries retained
num_data <- num_data %>%
  mutate(
    wat12_ = case_when(
      wat12_1 == 1 & wat12_2 == 0 & wat12_3 == 0 & wat12_4 == 0 & wat12_5 == 0 ~ 1,
      wat12_1 == 0 & wat12_2 == 1 & wat12_3 == 0 & wat12_4 == 0 & wat12_5 == 0 ~ 2,
      wat12_1 == 0 & wat12_2 == 0 & wat12_3 == 1 & wat12_4 == 0 & wat12_5 == 0 ~ 3,
      wat12_1 == 0 & wat12_2 == 0 & wat12_3 == 0 & wat12_4 == 1 & wat12_5 == 0 ~ 4,
      wat12_1 == 0 & wat12_2 == 0 & wat12_3 == 0 & wat12_4 == 0 & wat12_5 == 1 ~ 0,
      TRUE ~ 5
    )
  ) %>%
  relocate(wat12_, .before = wat12_1)
## Create binary wat12_6 "combined methods"
num_data <- num_data %>%
  mutate(
    wat12_6_ = case_when(
      wat12_ == 5 ~ 1,
      TRUE ~ 0
    )
  ) %>%
  relocate(wat12_6_, .after = wat12_5)
    
## wat15
# collapse into wat15_ * binaries retained
for(i in 1:nrow(num_data)) {
  if(num_data[i, "wat15_1"] == 1 & rowSums(num_data[i, c("wat15_2","wat15_3","wat15_4","wat15_5","wat15_6","wat15_7","wat15_8","wat15_9","wat15_10","wat15_11","wat15_12")]) == 0) {num_data[i, "wat15_"] = 1}
  else if(num_data[i, "wat15_2"] == 1 & rowSums(num_data[i, c("wat15_1","wat15_3","wat15_4","wat15_5","wat15_6","wat15_7","wat15_8","wat15_9","wat15_10","wat15_11","wat15_12")]) == 0) {num_data[i, "wat15_"] = 2}
  else if(num_data[i, "wat15_3"] == 1 & rowSums(num_data[i, c("wat15_1","wat15_2","wat15_4","wat15_5","wat15_6","wat15_7","wat15_8","wat15_9","wat15_10","wat15_11","wat15_12")]) == 0) {num_data[i, "wat15_"] = 3}
  else if(num_data[i, "wat15_4"] == 1 & rowSums(num_data[i, c("wat15_1","wat15_2","wat15_3","wat15_5","wat15_6","wat15_7","wat15_8","wat15_9","wat15_10","wat15_11","wat15_12")]) == 0) {num_data[i, "wat15_"] = 4}
  else if(num_data[i, "wat15_5"] == 1 & rowSums(num_data[i, c("wat15_1","wat15_2","wat15_3","wat15_4","wat15_6","wat15_7","wat15_8","wat15_9","wat15_10","wat15_11","wat15_12")]) == 0) {num_data[i, "wat15_"] = 5}
  else if(num_data[i, "wat15_6"] == 1 & rowSums(num_data[i, c("wat15_1","wat15_2","wat15_3","wat15_4","wat15_5","wat15_7","wat15_8","wat15_9","wat15_10","wat15_11","wat15_12")]) == 0) {num_data[i, "wat15_"] = 6}
  else if(num_data[i, "wat15_7"] == 1 & rowSums(num_data[i, c("wat15_1","wat15_2","wat15_3","wat15_4","wat15_5","wat15_6","wat15_8","wat15_9","wat15_10","wat15_11","wat15_12")]) == 0) {num_data[i, "wat15_"] = 7}
  else if(num_data[i, "wat15_8"] == 1 & rowSums(num_data[i, c("wat15_1","wat15_2","wat15_3","wat15_4","wat15_5","wat15_6","wat15_7","wat15_9","wat15_10","wat15_11","wat15_12")]) == 0) {num_data[i, "wat15_"] = 8}
  else if(num_data[i, "wat15_9"] == 1 & rowSums(num_data[i, c("wat15_1","wat15_2","wat15_3","wat15_4","wat15_5","wat15_6","wat15_7","wat15_8","wat15_10","wat15_11","wat15_12")]) == 0) {num_data[i, "wat15_"] = 9}
  else if(num_data[i, "wat15_10"] == 1 & rowSums(num_data[i, c("wat15_1","wat15_2","wat15_3","wat15_4","wat15_5","wat15_6","wat15_7","wat15_8","wat15_9","wat15_11","wat15_12")]) == 0) {num_data[i, "wat15_"] = 10}
  else if(num_data[i, "wat15_11"] == 1 & rowSums(num_data[i, c("wat15_1","wat15_2","wat15_3","wat15_4","wat15_5","wat15_6","wat15_7","wat15_8","wat15_9","wat15_10","wat15_12")]) == 0) {num_data[i, "wat15_"] = 11}
  else if(num_data[i, "wat15_12"] == 1 & rowSums(num_data[i, c("wat15_1","wat15_2","wat15_3","wat15_4","wat15_5","wat15_6","wat15_7","wat15_8","wat15_9","wat15_10","wat15_11")]) == 0) {num_data[i, "wat15_"] = 12}
  else {num_data[i, "wat15_"] = 13}
}
num_data <- num_data %>%
  relocate(wat15_, .before = wat15_1)
    
## liv4
# Collapse into liv4_ * binaries dropped
num_data <- num_data %>%
  mutate(
    liv4_ = case_when(
      liv4_1 == 0 & liv4_2 == 0 & liv4_3 == 1 ~ 0,
      liv4_1 == 1 & liv4_2 == 0 & liv4_3 == 0 ~ 1,
      liv4_1 == 0 & liv4_2 == 1 & liv4_3 == 0 ~ 2,
      TRUE ~ 3
    )
  ) %>%
  relocate(liv4_, .before = liv4_1) %>%
  select(-c(liv4_1, liv4_2, liv4_3))

## liv1
# Collapse into liv1_ * binaries retained
num_data <- num_data %>%
  mutate(
    liv1_ = case_when(
      liv1_1 == 1 & liv1_2 == 0 & liv1_3 == 0 & liv1_4 == 0 & liv1_5 == 0 & liv1_6 == 0 & liv1_7== 0 & liv1_8 == 0 & liv1_9 == 0  ~  1,
      liv1_1 == 0 & liv1_2 == 1 & liv1_3 == 0 & liv1_4 == 0 & liv1_5 == 0 & liv1_6 == 0 & liv1_7== 0 & liv1_8 == 0 & liv1_9 == 0  ~  2,
      liv1_1 == 0 & liv1_2 == 0 & liv1_3 == 1 & liv1_4 == 0 & liv1_5 == 0 & liv1_6 == 0 & liv1_7== 0 & liv1_8 == 0 & liv1_9 == 0  ~  3,
      liv1_1 == 0 & liv1_2 == 0 & liv1_3 == 0 & liv1_4 == 1 & liv1_5 == 0 & liv1_6 == 0 & liv1_7== 0 & liv1_8 == 0 & liv1_9 == 0  ~  4,
      liv1_1 == 0 & liv1_2 == 0 & liv1_3 == 0 & liv1_4 == 0 & liv1_5 == 1 & liv1_6 == 0 & liv1_7== 0 & liv1_8 == 0 & liv1_9 == 0  ~  5,
      liv1_1 == 0 & liv1_2 == 0 & liv1_3 == 0 & liv1_4 == 0 & liv1_5 == 0 & liv1_6 == 1 & liv1_7== 0 & liv1_8 == 0 & liv1_9 == 0  ~  6,
      liv1_1 == 0 & liv1_2 == 0 & liv1_3 == 0 & liv1_4 == 0 & liv1_5 == 0 & liv1_6 == 0 & liv1_7== 1 & liv1_8 == 0 & liv1_9 == 0  ~  7,
      liv1_1 == 0 & liv1_2 == 0 & liv1_3 == 0 & liv1_4 == 0 & liv1_5 == 0 & liv1_6 == 0 & liv1_7== 0 & liv1_8 == 1 & liv1_9 == 0  ~  8,
      liv1_1 == 0 & liv1_2 == 0 & liv1_3 == 0 & liv1_4 == 0 & liv1_5 == 0 & liv1_6 == 0 & liv1_7== 0 & liv1_8 == 0 & liv1_9 == 1  ~  0,
      TRUE ~ 9
    )
  ) %>%
  relocate(liv1_, .before = liv1_1) 
# create binary liv1_10_ "multiple income sources"
num_data <- num_data %>%
  mutate(
    liv1_10_ = case_when(
      liv1_ == 9 ~ 1,
      TRUE ~ 0
    )
  ) %>% 
  relocate(liv1_10_, .after = liv1_9)
    
# Replace NA vals ----
num_data[is.na(num_data)] <- "NA"

kable(head(num_data)) %>%
  kable_styling("striped", "condensed", full_width = FALSE) %>%
  scroll_box(width = "100%", height = "400px")

location	union	ward	village	camp	block	gps-latitude	gps-longitude	sex	sd1	sd2	sd3	sd4	sd5_	sd6_	sd7	sd8	sd9	sd11_1	sd11_2	sd11_3	sd12	sd13	sd14	sd15	sd16	sd17	sd18	sd20	sd21	sd22	w2	w4	w5	w7	w8	wat1	wat2	wat3	wat4	wat5	wat5a	wat5b	wat7	wat8	wat9	wat10	wat11	wat12_	wat12_2	wat12_4	wat14	wat14_1	wat14_2	wat14_3	wat15_	wat15_3	wat15_4	wat15_7	wat15_8	wat15_9	wat15_10	wat16	wat17	wb1	wb2	wb3	wb4	wb5	wb6	wb7	wb8	wb9	wb10	p1	p2	p3	p4	p5	pay1	pay2	pay3	hw	hw1	hw2	hw2a	hw3	hw4	hw5	hw6	hw7	hw8	hw9	hw10	hw11	hw12	hw13	liv1_	liv1_1	liv1_5	liv1_9	liv1_10_	liv2	liv3	liv4_	liv5	liv6
host	whykong	6	jimonkhali	NA	NA	21.05939	92.22692	male	1	4	40	2	6	2	2	2	NA	NA	NA	NA	1	2	3	0	1	1	0	1	5	4	0	1	0	1	business	3	3	3	2	3	42	1	1	salty water, iron	2	3	1	2	1	0	3	4	1	1 drum	13	0	0	0	0	1	1	the water is salty	water needs to be collected from remote area	2	2	4	3	2	2	3	3	3	3	1	1	1	1	1	3	1	6	3	2	2	2	1	1	1	1	1	2	1	1	1	1	1	0	0	0	1	0	1	3	2	because the water is salty, the shop has to collect water from far away	1 / pure water is not easy to drink because water is salty. 2 / there is a problem with cooking for water 3 / water is not easily available for business.
host	whykong	8	kharangkhali	NA	NA	21.04305	92.23357	female	2	4	30	2	6	3	2	0	NA	NA	NA	NA	2	3	2	0	1	1	0	2	2	3	0	1	0	1	covid-19	2	2	13	120	2	1680	7	1	the taste becomes salty, the color becomes red.	90	3	0	4	0	1	4	4	6	drum-01	13	1	1	0	0	0	0	the water level goes down.	i will make arrangements to collect water.	3	2	2	4	5	5	3	3	3	3	0	1	1	1	1	4	4	1	33	2	4	1	4	4	3	4	4	3	4	3	3	4	4	0	0	0	1	0	2	15	3	in the dry season the water decreases and the water becomes salty and red.	need to collect water from others villages
host	whykong	6	jimonkhali	NA	NA	21.05977	92.22733	female	2	4	50	2	6	1	3	2	2017	NA	NA	NA	1	3	2	0	3	0	0	2	4	8	0	1	1	1	i have to borrow	3	3	3	3	5	105	2	1	water is a little bit solty	3	7	1	2	1	0	2	5	2	1 drum	13	0	0	1	1	0	0	water dries up due to overcrowding and overuse.	it is difficult to collect water from a distance.	3	1	4	3	2	2	3	1	1	3	1	1	1	1	1	1	1	6	8	2	2	1	1	2	3	1	1	2	2	1	1	1	2	0	0	0	1	0	1	3	2	it is very difficult to drink pure water because the water is salty.	water has to be collected and brought from far away.
host	whykong	8	purbo moheskhalia para	NA	NA	21.04336	92.23361	female	1	3	57	1	6	5	6	1	NA	NA	NA	NA	4	1	4	1	1	1	1	1	4	4	1	1	0	1	covid 19	2	2	2	40	2	560	6	1	the water tastes salty and the water turns red.	40	2	0	4	0	1	3	5	4	2 dram, 2 water pot	13	1	1	0	0	0	0	the water level goes down.	arranges water supply.	3	2	3	3	4	6	3	3	3	4	0	1	1	1	1	4	4	3	27	4	4	1	3	3	3	3	3	3	3	2	2	3	4	0	0	0	1	0	2	2	3	the taste of water decreases and the color turns red.	i have collected water from the house next door.
host	whykong	8	purbo moheskhalia para	NA	NA	21.04334	92.23378	female	1	4	35	1	6	4	3	2	NA	NA	NA	NA	4	3	3	1	1	1	1	1	3	3	0	1	0	0	NA	2	15	13	30	2	420	5	1	the taste becomes salty and the color turns red.	120	4	0	4	0	1	10	10	0	one dram, 15 small bottol	13	1	1	0	0	0	0	going down the water level	i arrange water supply.	3	2	2	4	5	5	3	3	3	3	0	1	1	1	1	4	4	1	26	3	3	1	3	3	3	3	3	2	3	2	3	4	4	0	0	0	1	0	2	2	3	the amount decreases and the water turns red.	i supply water from the next village.
host	whykong	6	jimonkhali	NA	NA	21.05931	92.22797	male	1	4	20	1	6	0	2	0	1991	NA	NA	NA	4	3	1	0	3	0	0	2	5	3	0	0	1	1	i worked in agriculture and had accumulated rice and ate them. and some have had to borrow.	3	3	3	7	11	539	6	0	NA	7	8	0	4	0	1	4	4	4	4 water jar	13	0	0	1	1	1	0	water naturally becomes salt.	it is difficult to collect water from far away.	2	1	4	6	5	7	3	3	4	3	1	1	1	1	1	4	4	6	18	4	4	2	3	3	2	2	2	2	3	2	2	1	1	9	1	1	0	1	1	3	3	due to the low water content and the addition of water and salt, pure water and cooking are very difficult.	we were bound to use this salty water.

Question Data

A q_data object is created to store the question text for all items in the survey. The relevant row is extracted from raw_data and matched to the contents of num_data. String manipulation is performed using the stringR library (Wickham, 2022) including the following:

Capitalisation correction.
Special character removal.
Modifications to ensure brevity, consistency and enhance readability.

#extract question row
q_data <- raw_data[1, ]
    
# Match cols to num_data
q_data <- q_data[, colnames(q_data) %in% colnames(num_data)]

# String formatting
q_data <- q_data %>% mutate(across(everything(), 
                              ~ str_replace(., "income_source_past_12_months_", "income_source:_") %>%
                                str_replace("primary_way_household_treats_drinking_water","drinking_water_treatment:") %>%
                                str_replace("your_household_experience_the_fewest_water_problems", "fewest_water_problems:") %>%
                                str_replace("activities_are_you_engaged_in_for_household_food_production_or_income_generation", "income_generation:") %>%
                                str_replace("_are_","_") %>%
                                str_remove("_remittances") %>%
                                str_replace("_the_","_") %>%
                                str_replace("receiving_assistance","financial assistance: amount") %>%
                                str_replace("assistance_from_what_source", "financial assistance: source") %>%
                                str_replace("assistance_usage", "financial assistance: usage") %>%
                                str_remove("_for_heating-cooking")  %>%
                                str_remove("_food_for_work") %>%
                                str_remove("_agriculture_and_animal") %>%
                                str_remove("you_had_") %>%
                                str_remove("ngo") %>%
                                str_remove("do_you_think_it_is_for_you_") %>%
                                str_replace_all("_and_","&") %>%
                                str_replace_all("_"," ") %>%
                                str_to_title)) %>% 
  mutate(location = "Refugee Camp or Host Community",
         sd2 = "Relationship Status",
         sd4 = "Household: Responsible To Get Water",
         sd7 = "Household: Adult Members",
         sd8 = "Household: Elderly Members",
         sd9 = "Left Myanmar",
         sd13 = "House: Type",
         sd14 = "House: No. of Rooms",
         sd15 = "House: Has a Garden",
         sd16 = "House: Ownership",
         sd17 = "House: Electricity Supply",
         sd18 = "House: Piped Water Supply",
         sd19 = "House: Sewerage Connection",
         sd21 = "Rate Community: Socioeconomic Standing",
         sd22 = "Rate Community: Water Situation",
         w8 = "Experienced Problems & Solutions",
         p2 = "Women & Men: Equal Responsibility for Sanitation",
         p3 = "Women & Men: Equal Awareness of Feedback Processes",
         p4 = "Women & Men: Feedback Equally Valued",
         p5 = "Women & Men: Equal Awareness of Sanitation Rights",
         hw1 = "Worry About Water Supply",
         hw2 = "Supply Interruptions",
         hw2a = "Supply Interruptions: Expected or Unexpected",
         hw3 = "Unable to do Laundry Due to Water Situation",
         hw4 = "Schedule Change Due to Water Situation",
         hw5 = "Change What Was Eaten Due to Water Situation",
         hw6 = "Unable to Wash Hands After Dirty Activity",
         hw7 = "Unable to Wash Body Due to Water Situation",
         hw8 = "Not Enough Water to Drink",
         hw9 = "Felt Anger About Water Situation",
         hw10 = "Gone to Sleep Thirsty",
         hw11 = "No Useable or Drinkable Water",
         hw12 = "Felt Shame About Water Situation",
         hw13 = "Asked to Borrow Water",
         wat1 = "Drinking Water: Primary Source",
         wat2 = "Drinking Water: Secondary Source",
         wat3 = "Non-drinking Water: Primary Source",
         wat4 = "Drinking Water: Time to Source (mins)",
         wat5 = "Drinking Water: No.of Trips",
         wat9 = "Non-drinking Water: Time to Source (mins)",
         wat10 = "Non-drinking Water: No.of Trips",
         wat15_8 = "Fewest Water Problems: August",
         liv3 = "Income Generation: Primary Water Source",
         wb3 = "General Health",
         wb4 = "No. Days Poor Physical Health",
         wb5 = "No. Days Poor Mental Health",
         wb6 = "No. Days Health Prevented Normal Activities",
         wb7 = "Feel Unable to Control Important Things",
         wb8 = "Feel Confident in Ability to Control Problems",
         wb9 = "Feel Things Going Your Way",
         wb10 = "Feel Difficulties Could Not Be Overcome"
         )
    
# Add newly created variables
q_data <- q_data %>% mutate(
  sd5_ = "Household: Ensures Sufficient Water",
  sd6_ = "Household: Children Under 18",
  wat12_ = "Drinking Water Treatment",
  wat12_6_ = "Drinking Water Treatment: Multiple Methods",
  wat15_ = "Fewest Water Problems",
  hw = "Calculated HWISE Score",
  liv1_ = "Income Generation",
  liv1_10_ = "Income Generation: Multiple Activities",
  liv4_ = "Income Generation: Problematic Water Quantity or Quality",
  wat5a = "Drinking Water: Collection Time Per Week (mins)",
  wat5b = "Drinking Water: Collection Time Per Week (grouped)"
) %>%
  relocate(sd5_, .after = sd4) %>%
  relocate(sd6_, .after = sd5_) %>%
  relocate(wat12_, .before = wat12_1) %>%
  relocate(wat12_6_, .after = wat12_5) %>%
  relocate(wat15_, .before = wat15_1) %>%
  relocate(hw, .before = hw1) %>%
  relocate(liv1_, .before = liv1_1) %>%
  relocate(liv1_10_, .after = liv1_9) %>%
  relocate(liv4_, .after = liv3) %>%
  relocate(wat5a, .after = wat5) %>%
  relocate(wat5b, .after = wat5a)

kable(head(q_data)) %>%
  kable_styling("striped", "condensed", full_width = FALSE) %>%
  scroll_box(width = "100%", height = "400px")

	location	union	ward	village	camp	block	gps-latitude	gps-longitude	sex	sd1	sd2	sd3	sd4	sd5_	sd6_	sd7	sd8	sd9	sd11	sd11_1	sd11_2	sd11_3	sd12	sd13	sd14	sd15	sd16	sd17	sd18	sd19	sd20	sd21	sd22	w1_1	w2	w3	w4	w5	w6	w7	w8	p1	p2	p3	p4	p5	hw	hw1	hw2	hw2a	hw3	hw4	hw5	hw6	hw7	hw8	hw9	hw10	hw11	hw12	hw13	wat1	wat2	wat3	wat4	wat5	wat5a	wat5b	wat6	wat7	wat8	wat9	wat10	wat11	wat12_	wat12_1	wat12_2	wat12_3	wat12_4	wat12_5	wat12_6_	wat14	wat14_1	wat14_2	wat14_3	wat15_	wat15_1	wat15_2	wat15_3	wat15_4	wat15_5	wat15_6	wat15_7	wat15_8	wat15_9	wat15_10	wat15_11	wat15_12	wat16	wat17	liv1_	liv1_1	liv1_2	liv1_3	liv1_4	liv1_5	liv1_6	liv1_7	liv1_8	liv1_9	liv1_10_	liv2	liv3	liv4_	liv5	liv6	pay1	pay2	pay3	wb1	wb2	wb3	wb4	wb5	wb6	wb7	wb8	wb9	wb10
2	Refugee Camp or Host Community	Union	Ward	Village	Camp	Block	Gps-Latitude	Gps-Longitude	Sex	Head Of Hh	Relationship Status	Age	Household: Responsible To Get Water	Household: Ensures Sufficient Water	Household: Children Under 18	Household: Adult Members	Household: Elderly Members	Left Myanmar	Financial Assistance	Financial Assistance: Amount	Financial Assistance: Source	Financial Assistance: Usage	Highest Level Of Education	House: Type	House: No. of Rooms	House: Has a Garden	House: Ownership	House: Electricity Supply	House: Piped Water Supply	House: Sewerage Connection	Primary Fuel Source	Rate Community: Socioeconomic Standing	Rate Community: Water Situation	Income Source: Employment In Government	Income Source: Employment In Private Sector	Income Source: Casual Labour	Income Source: Own Business	Income Source: Farming	Income Source: From Family Relative	Difficult To Obtain Sufficient Income	Experienced Problems & Solutions	Water&Sanitation Needs Being Met	Women & Men: Equal Responsibility for Sanitation	Women & Men: Equal Awareness of Feedback Processes	Women & Men: Feedback Equally Valued	Women & Men: Equal Awareness of Sanitation Rights	Calculated HWISE Score	Worry About Water Supply	Supply Interruptions	Supply Interruptions: Expected or Unexpected	Unable to do Laundry Due to Water Situation	Schedule Change Due to Water Situation	Change What Was Eaten Due to Water Situation	Unable to Wash Hands After Dirty Activity	Unable to Wash Body Due to Water Situation	Not Enough Water to Drink	Felt Anger About Water Situation	Gone to Sleep Thirsty	No Useable or Drinkable Water	Felt Shame About Water Situation	Asked to Borrow Water	Drinking Water: Primary Source	Drinking Water: Secondary Source	Non-drinking Water: Primary Source	Drinking Water: Time to Source (mins)	Drinking Water: No.of Trips	Drinking Water: Collection Time Per Week (mins)	Drinking Water: Collection Time Per Week (grouped)	Injured While Fetching Water	Problem With Water Quality	The Water Quality Problem	Non-drinking Water: Time to Source (mins)	Non-drinking Water: No.of Trips	Treated Water To Make It Safer	Drinking Water Treatment	Drinking Water Treatment: Boil	Drinking Water Treatment: Filter	Drinking Water Treatment: Add Chemicals Chlorine Tablets	Drinking Water Treatment: Other Specify	Drinking Water Treatment: Do Nothing	Drinking Water Treatment: Multiple Methods	Kolosh	Buckets	Jerry Cans	Other	Fewest Water Problems	Fewest Water Problems: January	Fewest Water Problems: February	Fewest Water Problems: March	Fewest Water Problems: April	Fewest Water Problems: May	Fewest Water Problems: June	Fewest Water Problems: July	Fewest Water Problems: August	Fewest Water Problems: September	Fewest Water Problems: October	Fewest Water Problems: November	Fewest Water Problems: December	Main Cause Of Problems With Water	What Do You Do When You Dont Have Enough Water	Income Generation	Income Generation: Crop Production	Income Generation: Betel Nut Leaf Production	Income Generation: Livestock Production	Income Generation: Salt Production	Income Generation: Sea Fishing	Income Generation: Aquaculture Shrimp Farming	Income Generation: Aquaculture Fish Farming	Income Generation: Other	Income Generation: None Of Above	Income Generation: Multiple Activities	Do You Own Businesses	Income Generation: Primary Water Source	Income Generation: Problematic Water Quantity or Quality	Please Describe Problem	What You Did When You Experienced These Problems	Important To Pay To Install Your Own Water System	Important To Pay For Regular Costs	Fee Structure For Use Of Main System	How Do You Feel About Your Life	Change Would Make Biggest Positive Difference	General Health	No. Days Poor Physical Health	No. Days Poor Mental Health	No. Days Health Prevented Normal Activities	Feel Unable to Control Important Things	Feel Confident in Ability to Control Problems	Feel Things Going Your Way	Feel Difficulties Could Not Be Overcome

Text Data

A text_data object is created to store all data as readable text responses. This dataset is called to render intelligible labels and axis ticks during plot and visualisation creation. Data is read from the original Excel sheet and the following transformations applied:

Columns matched to num_data content.
Numeric response data converted to intelligible text responses.
String formatting including:
- Capitalisation correction.
- Special character removal.
- Modifications to ensure brevity, consistency and enhance readability.

# Read Excel
text_data <- try(xl.read.file("../data/demo_data.xlsx",
                 header = FALSE,
                 password =  "password",
                 top.left.cell = "A3")) # ignores row 1 & 2 (chr), ensures single accurate class per col

# Set colnames as question codes
colnames(text_data) <- colnames(raw_data)

# match cols to data in clean.data
text_data <- text_data[, colnames(text_data) %in% colnames(num_data)]

# Add newly created variables
`%!in%` <- Negate(`%in%`) # create 'not in' function
text_data <- bind_cols(text_data, num_data[, colnames(num_data) %!in% colnames(text_data)])
text_data <- text_data %>%
  relocate(sd5_, .after = sd4) %>%
  relocate(sd6_, .after = sd5_) %>%
  relocate(wat12_, .before = wat12_1) %>%
  relocate(wat12_6_, .after = wat12_5) %>%
  relocate(wat15_, .before = wat15_1) %>%
  relocate(hw, .before = hw1) %>%
  relocate(liv1_, .before = liv1_1) %>%
  relocate(liv1_10_, .after = liv1_9) %>%
  relocate(liv4_, .after = liv3) %>%
  relocate(wat5a, .after = wat5) %>%
  relocate(wat5b, .after = wat5a)

# Insert correct text responses
text_data$camp <- num_data$camp
text_data$sd11_2 <- num_data$sd11_2

text_data$sd5_ <- sapply(text_data$sd5_, function(x) {
  switch(x, "Self", "Spouse", "Children: Boy", "Children: Girl", "Other", "Shared Responsibility")})

text_data$sd9 <- num_data$sd9

## Define a list of all binary questions to format together
binary_q <- c("sd11","sd15","sd17","sd18","sd19","w1_1","w2","w3","w4","w5",
              "w6","w7","wat6","wat7","wat11","wat12_1","wat12_2","wat12_3","wat12_4",
              "wat12_5","wat12_6_","wat15_1","wat15_2","wat15_3","wat15_4","wat15_5","wat15_6",
              "wat15_7","wat15_8","wat15_9","wat15_10","wat15_11","wat15_12","liv1_1","liv1_2",
              "liv1_3","liv1_4","liv1_5","liv1_6","liv1_7","liv1_8","liv1_9","liv1_10_","liv2")
## format all binaries
text_data[binary_q] <- lapply(text_data[binary_q], function(x){
  x = replace(x, which(x==0),"No")
  x = replace(x, which(x==1),"Yes")
  x = replace(x, which(x==2),"Don't Know")
  x = replace(x, which(x==88),"NA")})

text_data$sd20 <- sapply(text_data$sd20, function(x) {
  x = replace(x, which(x==1),"Wood")
  x = replace(x, which(x==2),"Gas Bottles")
  x = replace(x, which(x==3),"Other")}) 

text_data[c("wat1","wat2","wat3","liv3")] <- lapply(text_data[c("wat1","wat2","wat3","liv3")], function(x){
  x = replace(x, which(x==1),"Piped Supply")
  x = replace(x, which(x==2),"Stand Pipe")
  x = replace(x, which(x==3),"Borehole/Tubewell")
  x = replace(x, which(x==4),"Dug Well: Protected")
  x = replace(x, which(x==5),"Dug Well: Unrotected")
  x = replace(x, which(x==6),"Spring: Protected")
  x = replace(x, which(x==7),"Spring: Unprotected")
  x = replace(x, which(x==8),"Rainwater Collection")
  x = replace(x, which(x==9),"Small Water Vendor")
  x = replace(x, which(x==10),"Tanker Truck")
  x = replace(x, which(x==11),"Bottled Water")
  x = replace(x, which(x==12),"Sachet Water")
  x = replace(x, which(x==13),"Surface Water/Pond/River/Lake")
  x = replace(x, which(x==14),"Other Person")
  x = replace(x, which(x==15),"Other")
  x = replace(x, which(is.na(x)),"NA")
})
## Change to grouped values 
text_data$wat5b <- sapply(text_data$wat5b, function(x){
  x = replace(x, which(x==1), "0-100 mins")
  x = replace(x, which(x==2), "101-200 mins")
  x = replace(x, which(x==3), "201-300 mins")
  x = replace(x, which(x==4), "301-400 mins")
  x = replace(x, which(x==5), "401-500 mins")
  x = replace(x, which(x==6), "501-600 mins")
  x = replace(x, which(x==7), "600+ mins")
})

text_data$wat12_ <- sapply(text_data$wat12_, function(x){
  switch(x+1, "None","Boil","Filter","Add Chemicals/Chlorine","Other","Combined Methods")})

text_data$wat15_ <- sapply(text_data$wat15_, function(x) {
  switch(x, "Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec","Multiple Months")})

text_data$liv1_ <- sapply(text_data$liv1_, function(x) {
  switch(x+1, "None of the Above", "Crop Production","Betel Nut/Leaf Production",
         "Livestock Production","Salt Production","Sea Fishing","Shrimp Farming",
         "Fish Farming","Other","Multiple Activities")})

text_data$liv4_ <- sapply(text_data$liv4_, function(x){
  switch(x+1, "No","Quantity","Quality","Both Quantity & Quality")})

text_data[, grepl("^hw.*\\d$", names(text_data))] <- apply(text_data[, grepl("^hw.*\\d$", names(text_data))], 2, function(x){
  x = replace(x, which(x=="0_times"), "Never")
  x = replace(x, which(x=="1-2_times"), "Rarely")
  x = replace(x, which(x=="3-10_times"), "Sometimes")
  x = replace(x, which(x=="11-20_times"), "Often")
  x = replace(x, which(x=="more_than_20_times"), "Always")
  x = replace(x, which(x=="dont_know"), "Don't Know")
})

text_data[, grepl("pay", names(text_data))] <- apply(text_data[,grepl("pay", names(text_data))], 2, function(x){
  x = replace(x, which(x=="Fixed_cost_per_amount"), "Fixed: Per Amount")
  x = replace(x, which(x=="Fixed_cost_per_month"), "Fixed: Per Month")
  x = replace(x, which(x=="VC_permonth_on_set_factors"), "Variable: Set Factors")
  x = replace(x, which(x=="Variable_cost_per_amount_or_use"), "Variable: Per Amount")
  x = replace(x, which(x=="no_fixed_payment_is_made"), "No Fixed Payment")
  x = replace(x, which(x=="it_is_free"), "Free")
})    

text_data <-  text_data %>% mutate(across(where(is.character),
                            ~ str_replace_all(., "_and_", "_&_") %>%
                              str_replace_all("_", " ") %>%
                              str_to_title)) %>% mutate(across(where(is.logical), str_to_title))

text_data <- text_data %>% mutate(across(c("w8","wat8","wat16","wat17","liv6","liv5"), 
                ~ str_replace_all(., "\n", " ") %>%
                  str_squish %>%
                  str_to_sentence))

text_data[is.na(text_data)] <- "NA"

kable(head(text_data)) %>%
  kable_styling("striped", "condensed", full_width = FALSE) %>%
  scroll_box(width = "100%", height = "400px")

location	union	ward	village	camp	block	gps-latitude	gps-longitude	sex	sd1	sd2	sd3	sd4	sd5_	sd6_	sd7	sd8	sd9	sd11	sd11_1	sd11_2	sd11_3	sd12	sd13	sd14	sd15	sd16	sd17	sd18	sd19	sd20	sd21	sd22	w1_1	w2	w3	w4	w5	w6	w7	w8	p1	p2	p3	p4	p5	hw	hw1	hw2	hw2a	hw3	hw4	hw5	hw6	hw7	hw8	hw9	hw10	hw11	hw12	hw13	wat1	wat2	wat3	wat4	wat5	wat5a	wat5b	wat6	wat7	wat8	wat9	wat10	wat11	wat12_	wat12_1	wat12_2	wat12_3	wat12_4	wat12_5	wat12_6_	wat14	wat14_1	wat14_2	wat14_3	wat15_	wat15_1	wat15_2	wat15_3	wat15_4	wat15_5	wat15_6	wat15_7	wat15_8	wat15_9	wat15_10	wat15_11	wat15_12	wat16	wat17	liv1_	liv1_1	liv1_2	liv1_3	liv1_4	liv1_5	liv1_6	liv1_7	liv1_8	liv1_9	liv1_10_	liv2	liv3	liv4_	liv5	liv6	pay1	pay2	pay3	wb1	wb2	wb3	wb4	wb5	wb6	wb7	wb8	wb9	wb10
Host	Whykong	6	Jimonkhali	Na	NA	21.05939	92.22692	Male	Myself	Married	40	My Spouse	Shared Responsibility	2	2	2	Na	No	NA	Na	NA	Primary School	Processed Wood	3	No	Owned	Yes	No	No	Wood	5	4	No	No	No	Yes	No	No	Yes	Business	True	True	True	True	True	3	Rarely	Rarely	Unexpected	Never	Never	Never	Never	Never	Rarely	Never	Never	Never	Never	Never	Borehole/Tubewell	Borehole/Tubewell	Borehole/Tubewell	2	3	42	0-100 Mins	No	Yes	Salty water, iron	2	3	Yes	Filter	No	Yes	No	No	No	No	3	4	1	1 Drum	Multiple Months	No	No	No	No	No	No	No	No	Yes	Yes	No	No	The water is salty	Water needs to be collected from remote area	None Of The Above	No	No	No	No	No	No	No	No	Yes	No	Yes I Own The Businesses	Borehole/Tubewell	Quality	Because the water is salty, the shop has to collect water from far away	1 / pure water is not easy to drink because water is salty. 2 / there is a problem with cooking for water 3 / water is not easily available for business.	Somewhat Important	Not Important At All	Free	Somewhat Dissatisfied	Safe Water Services	Very Good	3	2	2	Fairly Often	Fairly Often	Fairly Often	Fairly Often
Host	Whykong	8	Kharangkhali	Na	NA	21.04305	92.23357	Female	My Spouse	Married	30	My Spouse	Shared Responsibility	3	2	0	Na	No	NA	Na	NA	Secondary School	Wood/Canvas/Plastic	2	No	Owned	Yes	No	No	Gas Bottles	2	3	No	No	No	Yes	No	No	Yes	Covid-19	False	True	True	True	True	33	Rarely	Often	Announced/Scheduled	Often	Often	Sometimes	Often	Often	Sometimes	Often	Sometimes	Sometimes	Often	Often	Stand Pipe	Stand Pipe	Surface Water Pond River Lake	120	2	1680	600+ Mins	No	Yes	The taste becomes salty, the color becomes red.	90	3	No	Other	No	No	No	Yes	No	No	4	4	6	Drum-01	Multiple Months	No	No	Yes	Yes	No	No	No	No	No	No	No	No	The water level goes down.	I will make arrangements to collect water.	None Of The Above	No	No	No	No	No	No	No	No	Yes	No	No I Work For Somebody Else	Other	Both Quantity & Quality	In the dry season the water decreases and the water becomes salty and red.	Need to collect water from others villages	Very Important	Very Important	Fixed: Per Month	Somewhat Satisfied	Safe Water Services	Fair	4	5	5	Fairly Often	Fairly Often	Fairly Often	Fairly Often
Host	Whykong	6	Jimonkhali	Na	NA	21.05977	92.22733	Female	My Spouse	Married	50	My Spouse	Shared Responsibility	1	3	2	2017	No	NA	Na	NA	Primary School	Wood/Canvas/Plastic	2	No	Allocated/Given By Authorities	No	No	No	Gas Bottles	4	8	No	No	No	Yes	Yes	No	Yes	I have to borrow	True	True	True	True	True	8	Rarely	Rarely	Announced/Scheduled	Never	Rarely	Sometimes	Never	Never	Rarely	Rarely	Never	Never	Never	Rarely	Borehole/Tubewell	Borehole/Tubewell	Borehole/Tubewell	3	5	105	101-200 Mins	No	Yes	Water is a little bit solty	3	7	Yes	Filter	No	Yes	No	No	No	No	2	5	2	1 Drum	Multiple Months	No	No	No	No	No	No	Yes	Yes	No	No	No	No	Water dries up due to overcrowding and overuse.	It is difficult to collect water from a distance.	None Of The Above	No	No	No	No	No	No	No	No	Yes	No	Yes I Own The Businesses	Borehole/Tubewell	Quality	It is very difficult to drink pure water because the water is salty.	Water has to be collected and brought from far away.	Not Important At All	Not Important At All	Free	Somewhat Satisfied	Better Health Services	Very Good	3	2	2	Fairly Often	Almost Never	Almost Never	Fairly Often
Host	Whykong	8	Purbo Moheskhalia Para	Na	NA	21.04336	92.23361	Female	Myself	Widowed	57	Myself	Shared Responsibility	5	6	1	Na	No	NA	Na	NA	No Formal Education	Concrete/Brick	4	Yes	Owned	Yes	Yes	No	Wood	4	4	No	Yes	No	Yes	No	No	Yes	Covid 19	False	True	True	True	True	27	Often	Often	Announced/Scheduled	Sometimes	Sometimes	Sometimes	Sometimes	Sometimes	Sometimes	Sometimes	Rarely	Rarely	Sometimes	Often	Stand Pipe	Stand Pipe	Stand Pipe	40	2	560	501-600 Mins	No	Yes	The water tastes salty and the water turns red.	40	2	No	Other	No	No	No	Yes	No	No	3	5	4	2 Dram, 2 Water Pot	Multiple Months	No	No	Yes	Yes	No	No	No	No	No	No	No	No	The water level goes down.	Arranges water supply.	None Of The Above	No	No	No	No	No	No	No	No	Yes	No	No I Work For Somebody Else	Stand Pipe	Both Quantity & Quality	The taste of water decreases and the color turns red.	I have collected water from the house next door.	Very Important	Very Important	Variable: Set Factors	Somewhat Satisfied	Safe Water Services	Good	3	4	6	Fairly Often	Fairly Often	Fairly Often	Very Often
Host	Whykong	8	Purbo Moheskhalia Para	Na	NA	21.04334	92.23378	Female	Myself	Married	35	Myself	Shared Responsibility	4	3	2	Na	No	NA	Na	NA	No Formal Education	Wood/Canvas/Plastic	3	Yes	Owned	Yes	Yes	No	Wood	3	3	No	No	No	Yes	No	No	No	NA	False	True	True	True	True	26	Sometimes	Sometimes	Announced/Scheduled	Sometimes	Sometimes	Sometimes	Sometimes	Sometimes	Rarely	Sometimes	Rarely	Sometimes	Often	Often	Stand Pipe	Other	Surface Water Pond River Lake	30	2	420	401-500 Mins	No	Yes	The taste becomes salty and the color turns red.	120	4	No	Other	No	No	No	Yes	No	No	10	10	0	One Dram, 15 Small Bottol	Multiple Months	No	No	Yes	Yes	No	No	No	No	No	No	No	No	Going down the water level	I arrange water supply.	None Of The Above	No	No	No	No	No	No	No	No	Yes	No	No I Work For Somebody Else	Stand Pipe	Both Quantity & Quality	The amount decreases and the water turns red.	I supply water from the next village.	Very Important	Very Important	Fixed: Per Month	Somewhat Satisfied	Safe Water Services	Fair	4	5	5	Fairly Often	Fairly Often	Fairly Often	Fairly Often
Host	Whykong	6	Jimonkhali	Na	NA	21.05931	92.22797	Male	Myself	Married	20	Myself	Shared Responsibility	0	2	0	1991	No	NA	Na	NA	No Formal Education	Wood/Canvas/Plastic	1	No	Allocated/Given By Authorities	No	No	No	Gas Bottles	5	3	No	No	No	No	Yes	No	Yes	I worked in agriculture and had accumulated rice and ate them. And some have had to borrow.	True	True	True	True	True	18	Often	Often	Unexpected	Sometimes	Sometimes	Rarely	Rarely	Rarely	Rarely	Sometimes	Rarely	Rarely	Never	Never	Borehole/Tubewell	Borehole/Tubewell	Borehole/Tubewell	7	11	539	501-600 Mins	No	No	NA	7	8	No	Other	No	No	No	Yes	No	No	4	4	4	4 Water Jar	Multiple Months	No	No	No	No	No	No	Yes	Yes	Yes	No	No	No	Water naturally becomes salt.	It is difficult to collect water from far away.	Multiple Activities	Yes	No	No	No	Yes	No	No	No	No	Yes	Yes I Own The Businesses	Borehole/Tubewell	Both Quantity & Quality	Due to the low water content and the addition of water and salt, pure water and cooking are very difficult.	We were bound to use this salty water.	Very Important	Very Important	Free	Somewhat Dissatisfied	Better Health Services	Very Good	6	5	7	Fairly Often	Fairly Often	Very Often	Fairly Often

Mapping

Interactive mapping of geospatial data is achieved using the leaflet library (Cheng, Karambelkar and Xie, 2022). Layer controls allow the user to select from three different base maps. Polygon layers can be selected to visualise the boundaries of refugee camps (ISCG, 2022) and administrative regions (OCHA, 2020). Additional data is imported to enable the user to visualise the locations of existing sanitation facilities (OCHA, 2022).

Mapped data points can be colour coded according to any variable in the dataset and an appropriate legend is created.

All points can be selected to display a tool-tip containing more detailed information.

# Read shape files
camps <- st_read(dsn = "../data/shapefiles/camp", layer = "T220130_RRC_Outline_Camp_AL1")
camps <- subset(camps, camps$Upazila=="Teknaf")

unions <- st_read(dsn = "../data/shapefiles/union", layer = "bgd_admbnda_adm4_bbs_20201113")
unions <- subset(unions, unions$ADM4_EN %in% c("Baharchhara","Nhilla","Sabrang","Teknaf","Teknaf Paurashava","Whykong"))

infra <- st_read(dsn = "../data/shapefiles/infra", layer = "WASH_Infras_LT_Bath_TW_May_31_2022")
infra <- subset(infra, infra$Upazila=="Teknaf") %>% infra[c(1:33)]

# replacements for consistency
infra$Type_Faci <- sapply(infra$Type_Faci, function(x) {
  x = replace(x, which(x=="latrine"),"Latrine")
  x = replace(x, which(x=="Tubewell-Handpump"), "Handpump Tubewell")
  x = replace(x, which(x=="Both (Latrine & Bathing)"), "Latrine & Bathing")
})

# create base map
base_map <- leaflet(data = text_data) %>% 
  setView(lng = text_data[1, "gps-longitude"],
          lat = text_data[1, "gps-latitude"],
          zoom=12) %>%
  addTiles(group = "Default Map") %>%
  addProviderTiles("CartoDB.Positron", group = "Minimal Map") %>%
  addProviderTiles("Esri.WorldImagery", group = "Satelite Map")%>% 
  addScaleBar(position = "bottomleft",
              options = scaleBarOptions(maxWidth=400)) %>%
  addMiniMap(position = "bottomright", toggleDisplay = TRUE, width = 200) %>%
  
# add infrastructure markers  
  addCircleMarkers(data = infra[infra$Type_Faci=="Bathing Cubicle" | infra$Type_Faci=="Latrine & Bathing", ],
                       ~Long,
                       ~Lat,
                       radius = 3,
                       stroke = T,
                       weight = 1, opacity = 1,
                       fillOpacity = 0,
                       group = "Bathing Cubicle",
                       popup=~paste(
                         "<b>", "Agency: ",Agency,"</b><br/>",
                         "Facility: ", Type_Faci, "<br/>",
                         "Sub Type: ",Sub_Type_F, "<br/>",
                         "Bathing Total: ", Bathing, "<br/>",
                         "Female: ", Bathing_F, "<br/>",
                         "Male: ", Bathing_M, "<br/>",
                         "Universal: ", Bath_gen_u, "<br/>"))%>%
  addCircleMarkers(data = infra[infra$Type_Faci=="Latrine" | infra$Type_Faci=="Latrine & Bathing", ],
                       ~Long,
                       ~Lat,
                       radius = 3,
                       stroke = T,
                       weight = 1, opacity = 1,
                       fillOpacity = 0,
                       color="green",
                       group = "Latrine",
                       popup=~paste(
                         "<b>", "Agency: ",Agency,"</b><br/>",
                         "Facility: ", Type_Faci, "<br/>",
                         "Sub Type: ",Sub_Type_F, "<br/>",
                         "Latrines: ", LT, "<br/>",
                         "Female: ", LT_F, "<br/>",
                         "Male: ", LT_M, "<br/>",
                         "Universal: ", LT_Gen_uns, "<br/>",
                         "Slabs: ", Slabs, "<br/>",
                         "Rings: ", Rings, "<br/>",
                         "Structure: ", struc_wall, ", ", struc_pill, "<br/>",
                         "Volume (m3): ", Volume_M3, "<br/>"))%>%
  addCircleMarkers(data = infra[infra$Type_Faci=="Handpump Tubewell", ],
                       ~Long,
                       ~Lat,
                       radius = 3,
                       stroke = T,
                       weight = 1, opacity = 1,
                       fillOpacity = 0,
                       color="red",
                       group = "Handpump Tubewell",
                       popup=~paste(
                         "<b>", "Agency: ",Agency,"</b><br/>",
                         "Facility: ", Type_Faci, "<br/>",
                         "Sub Type: ",Sub_Type_F, "<br/>",
                         "TW_Depth: ", Depth_TW_F, "<br/>"))%>%
# add polygons
  addPolygons(data = camps,
                  color = "#800080", weight = 2, smoothFactor = 0.5, opacity = 1, 
                  fill= FALSE, group = "Camp Boundaries") %>%
  addPolygons(data = unions,
                  color = "#cc0000", weight = 2, smoothFactor = 0.5, opacity = 1, 
                  fill= FALSE, group = "Administrative Boundaries") %>%

# add UI toggles  
  addLayersControl(baseGroups = c("Default Map","Satelite Map", "Minimal Map"),
                       overlayGroups = c("Camp Boundaries", "Administrative Boundaries", "Bathing Cubicle", "Latrine", "Handpump Tubewell"),
                       options = layersControlOptions(collapsed = FALSE)) %>%
  hideGroup(c("Bathing Cubicle", "Latrine", "Handpump Tubewell"))

pal1 <- colorRampPalette(c("#cc0000","#ff1a1a","#ffa500","#ffd700","#00ff00","#00ced1","#003366"))
# colorData <- filtered_data()[[input$circle_color]]
color <- colorNumeric(rev(pal1(6)), domain = num_data[["hw"]])
legend_vals = text_data[["hw"]]
      
m1 <- base_map %>%
      addCircleMarkers(data = text_data,
                       text_data[, "gps-longitude"],
                       text_data[, "gps-latitude"],
                       radius = 4,
                       stroke = FALSE,
                       group = "points",
                       fillOpacity=1,
                       popup = ~paste(
                         "<b>", "HWISE Score: ","</b>",hw,"<br/>",
                         "<b>","Primary source (drinking): ","</b>",wat1, "<br/>",
                         "<b>","Primary source (non-drinking): ","</b>",wat3, "<br/>",
                         "<b>","Collection Time (drinking, weekly): ","</b>",wat5a, "<br/>",
                         "<b>","Treatment (drinking): ","</b>",wat12_, "<br/>",
                         "<b>","Quality problems: ","</b>",wat8, "<br/>",
                         "<b>","Supply interruptions: ","</b>",hw2, "<br/>",
                         "<b>","No useable/drinkable water: ","</b>",hw11, "<br/>",
                         "<b>","Borrow water: ","</b>",hw13, "<br/>"
                       ),
                       color = ~color(text_data[["hw"]])) %>%
      addLegend("topright",
                pal=color,
                values=legend_vals,
                opacity = 1,
                title=q_data[["hw"]],
                layerId="colorLegend")

Example Map (interactive)

An example map illustrating mapped points colour-coded according to overall water insecurity score.

Graphing

Plots are created using the ggplot2 library (Wickham, 2016). Three chart types, commensurate with end user’s statistical knowledge, can be generated.

All displayed variables can be configured by the user including x and y variables (where relevant) as well as an optional third grouping variable.

All charts are rendered as interactive objects through the use of the plotly library (Sievert, 2020). Interactive features include:

Tool-tips displayed on hover-over.
Click and drag to zoom.
Clickable legend to toggle chart elements.
Export plot to .png

Example Plots (interactive)

# Set theme 
my_theme <- theme(plot.title = element_text(size=13, color="#464646",face="bold", hjust=0.5),
                  axis.title = element_text(size=13, color="#464646"),
                  axis.text = element_text(size=12, color="#464646"),
                  axis.text.x = element_text(angle = 45, vjust = 1, hjust = 1),
                  legend.title = element_text(size=13, color="#464646",face="bold",hjust=0.5),
                  legend.text = element_text(size=12, color="#464646"),
                  legend.background = element_rect(fill = "#f2f2f2"),
                  panel.background = element_rect(fill = "#f2f2f2"),
                  plot.margin = margin(1,1,1,1, "cm"))

paletteFunc <- colorRampPalette(c("#18db50","#1d6cac","#00b4ff"))

Bar Plot

p1 <- # categorical x, not grouped
      num_data %>% 
      ggplot +
      aes(factor(hw2)) +
      geom_bar()+
      geom_bar(fill = paletteFunc(length(unique(num_data[["hw2"]])))) +
      scale_x_discrete(breaks = num_data[["hw2"]],
                       labels = text_data[["hw2"]]) +
      labs(title = paste0(q_data["hw2"]),
           x = NULL) +
      my_theme
      
ggplotly(p1, tooltip = "y")

Box Plot

p2 <- num_data %>% 
  ggplot +
  aes(x = factor(hw1), y = hw, fill = location)+
  geom_boxplot(varwidth = TRUE,
               alpha = 0.8,
               outlier.size = 2,
               outlier.shape = 8,
               outlier.colour = "black") +
  labs(title = paste0(q_data["hw1"]),
       x = NULL,
       y = paste0(q_data["hw"]),
       fill = "")+
    scale_fill_manual(values = paletteFunc(length(levels(factor(num_data[["location"]]))))) +
          scale_x_discrete(breaks = num_data[["hw1"]], 
                           labels = text_data[["hw1"]]) +
          #scale_y_continuous(breaks = seq(0,max(y,na.rm=T),ceiling(max(y,na.rm=T)/10))) +
          my_theme
  
ggplotly(p2) %>% layout(boxmode = "group")

Scatter Plot

p3 <- num_data %>%
  ggplot +
  aes(x = wat5, y = hw, color = camp) +
  geom_jitter(size = 1, width=0.1, height=0.1 )+
  geom_smooth(method = lm, color = "#ffa500") +
  labs(title = paste0(q_data[["wat5"]], " ~ ",q_data[["hw"]]),
       x = paste0(q_data[["wat5"]]),
       y = paste0(q_data[["hw"]]),
       color = paste0(q_data[["camp"]]))  + 
  scale_color_manual(values = paletteFunc(length(unique(num_data[["camp"]])))) + 
  my_theme
  
ggplotly(p3) %>% layout(legend = list(font = list(size = 12)))

Summary Statistics

Summary statistics are generated using the skimR library (Waring et al., 2022). Given the end users’ limited statistical knowledge, extensive descriptive statistics are not required. Range, mean, median and standard deviation provide a sufficient summary of central tendency and variation of data that is in-line with users’ existing knowledge.

Statistics for numeric and categorical data are displayed separately and provide the user with greater control over which data are displayed. An optional grouping variable can be selected and applied to display summary statistics according to specified subgroups.

# Define custom skim (display full names for factor levels in cat data)
my_skim <- skim_with(factor = sfl(top_counts = ~top_counts(., max_char = 25, max_levels = 50)),numeric = list(hist = NULL))

# create all summary stats
sum_df <- text_data %>%
      mutate(across(which(sapply(.,class)!="numeric"),factor)) %>%
      select(-c("gps-longitude","gps-latitude",sd11_3,w8,wat8,wat16,wat17,liv5,liv6)) %>%
      #group_by_at(group_val) %>%
      my_skim %>%
      focus(c(
        skim_variable,
        #all_of(group_val),
        n_missing,
        numeric.mean,
        numeric.p0,
        numeric.p50,
        numeric.p100,
        numeric.sd,
        factor.n_unique,
        factor.top_counts))

# rename columns
sum_df <- data.frame(sum_df) %>%
  rename("Type" = "skim_type",
         "Variable" = "skim_variable",
         "Missing" = "n_missing",
         "Mean" = "numeric.mean",
         "Min" = "numeric.p0",
         "Median" = "numeric.p50",
         "Max" = "numeric.p100",
         "Standard Deviation" = "numeric.sd",
         "Category Levels" = "factor.n_unique",
         "Category Level Counts" = "factor.top_counts")

# round numeric data
sum_df <- sum_df %>% mutate_if(is.numeric, round, 1)

Numeric Data

# numeric data
sum_num <- sum_df%>%
  filter(Type == "numeric") %>%
  select(-c(`Category Levels`,`Category Level Counts`, `Type`))%>%
  mutate(Question = sapply(Variable, function(x) q_data[[x]])) %>% # add question text column
  relocate(Question, .after = Variable)

kable(sum_num) %>%
  kable_styling("striped", "condensed", full_width = FALSE) %>%
  scroll_box(width = "100%", height = "400px")

Variable	Question	Mean	Min	Median	Max	Standard Deviation
sd3	Age	38.4	17	36	80	11.7
sd6_	Household: Children Under 18	3.2	0	3	11	1.8
sd7	Household: Adult Members	2.9	0	2	14	1.6
sd8	Household: Elderly Members	1.1	0	1	10	1.1
sd14	House: No. of Rooms	2.5	1	2	12	1.0
sd21	Rate Community: Socioeconomic Standing	2.7	0	2	8	1.5
sd22	Rate Community: Water Situation	3.0	0	3	9	1.8
hw	Calculated HWISE Score	19.0	0	19	39	9.7
wat4	Drinking Water: Time to Source (mins)	15.3	0	10	120	17.2
wat5	Drinking Water: No.of Trips	2.6	0	2	23	2.7
wat5a	Drinking Water: Collection Time Per Week (mins)	295.2	0	140	8400	623.2
wat9	Non-drinking Water: Time to Source (mins)	15.6	0	10	135	17.7
wat10	Non-drinking Water: No.of Trips	3.0	0	2	20	2.6
wat14	Kolosh	2.7	0	2	10	1.6
wat14_1	Buckets	3.0	0	3	11	1.7
wat14_2	Jerry Cans	1.3	0	0	15	1.9
wb4	No. Days Poor Physical Health	4.6	0	4	60	4.8
wb5	No. Days Poor Mental Health	4.1	0	2	30	4.8
wb6	No. Days Health Prevented Normal Activities	3.1	0	2	30	3.8

Categorical Data

# categorical data 
sum_cat <- sum_df%>%
  filter(Type == "factor") %>%
  select(-c(`Type`,`Mean`,`Min`,`Median`, `Max`, `Standard Deviation`))%>%
  mutate(Question = sapply(Variable, function(x) q_data[[x]])) %>% # add question text column
  relocate(Question, .after = Variable)

kable(sum_cat) %>%
  kable_styling("striped", "condensed", full_width = FALSE) %>%
  scroll_box(width = "100%", height = "400px")

Variable	Question	Category Levels	Category Level Counts
location	Refugee Camp or Host Community	2	Host: 235, Camp: 225
union	Union	7	NA: 225, Sabrang: 60, Whykong: 59, Nhila: 56, Teknaf: 25, Teknaf Municipality: 25, Baharchhara: 10
ward	Ward	10	NA: 225, 5: 56, 3: 44, 1: 41, 6: 41, 2: 28, 4: 12, 8: 6, 7: 5, 9: 2
village	Village	53	NA: 225, Puran Para: 23, West Sikdar Para: 21, Natmura Para: 13, Pahartoli: 9, Jimonkhali: 8, Kataboniya Para: 8, College Para: 7, Digliya Para: 7, Ghula Para: 7, Naytong Para: 7, Purbo Moheskhalia Para: 7, East Pahadtoli: 6, Hariya Khali: 6, Fuler Deil: 5, Jahaliya Para: 5, Solish Para: 5, Balukhali: 4, Bazar Para: 4, Horaci Para: 4, Katakhali: 4, Lombabil: 4, Moheshkalia Para: 4, Monir Ghuna: 4, Poran Pollon Para: 4, Uncchiprang: 4, Adorsho Gram: 3, Chandoli Para: 3, Chowdhury Para: 3, East Natmora Para: 3, Habir Chora: 3, Keti Bil: 3, Panchori Para: 3, Uttar Noya Para: 3, Uttor Para: 3, Ajiya Khali: 2, Dail Para: 2, Diallarbil: 2, Ghunar Para: 2, Islamabad: 2, Kanjor Para: 2, Kosobaniya Para: 2, Major Para: 2, Puran Bazar: 2, Puraton Pollan Para: 2, Choto Habi Para: 1, Jalia Para: 1, Kharangkhali: 1, Lafarghona: 1, Lengur Bill: 1
camp	Camp	6	Na: 235, Camp 26: 71, Camp 24: 58, Nayapara Refugee Camp: 49, Camp 27: 24, Camp 25: 23
block	Block	8	NA: 235, C: 67, D: 64, B: 36, A: 27, G: 16, E: 13, F: 2
sex	Sex	2	Female: 284, Male: 176
sd1	Head Of Hh	5	Myself: 266, My Spouse: 182, Other: 6, A Grandparent: 4, An Adult Child: 2
sd2	Relationship Status	4	Married: 417, Widowed: 29, Divorced: 8, Single: 6
sd4	Household: Responsible To Get Water	4	Myself: 238, My Spouse: 195, Other: 16, Adult Child: 11
sd5_	Household: Ensures Sufficient Water	6	Shared Responsibility: 267, Self: 110, Spouse: 64, Children: Girl: 10, Other: 6, Children: Boy: 3
sd9	Left Myanmar	33	Na: 235, 2017: 115, 1992: 20, 1991: 10, 2018: 7, 2007: 6, 1990: 5, 2008: 5, 1987: 4, 1989: 4, 2003: 4, 2006: 4, 2014: 4, 1994: 3, 1995: 3, 2001: 3, 2009: 3, 1988: 2, 1993: 2, 1996: 2, 1997: 2, 1998: 2, 2002: 2, 2005: 2, 2012: 2, 2016: 2, 1980: 1, 1981: 1, 2000: 1, 2004: 1, 2010: 1, 2013: 1, 2015: 1
sd11	Financial Assistance	2	No: 366, Yes: 94
sd11_1	Financial Assistance: Amount	25	NA: 366, 0: 46, 1050: 16, 500: 5, 700: 3, 750: 3, 4000: 2, 6000: 2, 1000: 1, 10000: 1, 1015: 1, 1030: 1, 1500: 1, 2000: 1, 20000: 1, 2400: 1, 28000: 1, 3000: 1, 30000: 1, 4500: 1, 5000: 1, 5200: 1, 5600: 1, 8000: 1, 9000: 1
sd11_2	Financial Assistance: Source	4	Na: 367, Ngo: 75, Government: 14, Old Age Allowance: 4
sd12	Highest Level Of Education	4	No Formal Education: 265, Primary School: 141, Secondary School: 48, University College: 6
sd13	House: Type	4	Wood/Canvas/Plastic: 279, Other: 82, Concrete/Brick: 79, Processed Wood: 20
sd15	House: Has a Garden	2	No: 371, Yes: 89
sd16	House: Ownership	4	Owned: 226, Allocated/Given By Author: 176, Rented: 55, Other: 3
sd17	House: Electricity Supply	2	Yes: 242, No: 218
sd18	House: Piped Water Supply	2	No: 335, Yes: 125
sd19	House: Sewerage Connection	2	No: 352, Yes: 108
sd20	Primary Fuel Source	2	Gas Bottles: 270, Wood: 190
w1_1	Income Source: Employment In Government	2	No: 459, Yes: 1
w2	Income Source: Employment In Private Sector	2	No: 413, Yes: 47
w3	Income Source: Casual Labour	2	Yes: 328, No: 132
w4	Income Source: Own Business	2	No: 378, Yes: 82
w5	Income Source: Farming	2	No: 382, Yes: 78
w6	Income Source: From Family Relative	2	No: 445, Yes: 15
w7	Difficult To Obtain Sufficient Income	2	Yes: 317, No: 143
p1	Water&Sanitation Needs Being Met	2	False: 260, True: 200
p2	Women & Men: Equal Responsibility for Sanitation	2	True: 332, False: 128
p3	Women & Men: Equal Awareness of Feedback Processes	2	True: 316, False: 144
p4	Women & Men: Feedback Equally Valued	2	True: 350, False: 110
p5	Women & Men: Equal Awareness of Sanitation Rights	2	True: 352, False: 108
hw1	Worry About Water Supply	5	Sometimes: 154, Often: 123, Rarely: 93, Never: 56, Always: 34
hw2	Supply Interruptions	5	Sometimes: 171, Rarely: 113, Often: 108, Never: 53, Always: 15
hw2a	Supply Interruptions: Expected or Unexpected	3	Unexpected: 250, Announced/Scheduled: 157, NA: 53
hw3	Unable to do Laundry Due to Water Situation	5	Sometimes: 169, Often: 117, Rarely: 101, Never: 59, Always: 14
hw4	Schedule Change Due to Water Situation	5	Sometimes: 183, Rarely: 140, Never: 69, Often: 63, Always: 5
hw5	Change What Was Eaten Due to Water Situation	5	Sometimes: 165, Rarely: 146, Never: 81, Often: 64, Always: 4
hw6	Unable to Wash Hands After Dirty Activity	4	Rarely: 183, Never: 121, Sometimes: 98, Often: 58
hw7	Unable to Wash Body Due to Water Situation	5	Sometimes: 171, Rarely: 136, Never: 84, Often: 65, Always: 4
hw8	Not Enough Water to Drink	5	Rarely: 146, Sometimes: 146, Never: 101, Often: 62, Always: 5
hw9	Felt Anger About Water Situation	5	Rarely: 154, Sometimes: 134, Never: 125, Often: 46, Always: 1
hw10	Gone to Sleep Thirsty	5	Rarely: 177, Never: 165, Sometimes: 83, Often: 33, Always: 2
hw11	No Useable or Drinkable Water	5	Sometimes: 157, Rarely: 153, Never: 87, Often: 58, Always: 5
hw12	Felt Shame About Water Situation	5	Rarely: 169, Sometimes: 125, Never: 99, Often: 61, Always: 6
hw13	Asked to Borrow Water	5	Rarely: 162, Sometimes: 148, Never: 86, Often: 56, Always: 8
wat1	Drinking Water: Primary Source	9	Stand Pipe: 217, Borehole/Tubewell: 119, Piped Water To Dwelling: 93, Other Person: 15, Protected Dug Well: 7, Other: 4, Small Water Vendor: 2, Tanker Truck: 2, Surface Water Pond River : 1
wat2	Drinking Water: Secondary Source	11	Stand Pipe: 143, Borehole/Tubewell: 110, Piped Water To Dwelling: 88, Other Person: 63, Surface Water Pond River : 24, Protected Dug Well: 12, Other: 7, Unprotected Dug Well: 5, Bottled Water: 4, Rainwater Collection: 2, Small Water Vendor: 2
wat3	Non-drinking Water: Primary Source	10	Stand Pipe: 160, Borehole/Tubewell: 121, Piped Water To Dwelling: 97, Surface Water Pond River : 40, Protected Dug Well: 14, Other Person: 12, Other: 7, Unprotected Dug Well: 6, Rainwater Collection: 2, Tanker Truck: 1
wat5b	Drinking Water: Collection Time Per Week (grouped)	7	0-100 Mins: 189, 201-300 Mins: 70, 101-200 Mins: 69, 600+ Mins: 59, 401-500 Mins: 33, 301-400 Mins: 23, 501-600 Mins: 17
wat6	Injured While Fetching Water	2	No: 318, Yes: 142
wat7	Problem With Water Quality	2	No: 301, Yes: 159
wat11	Treated Water To Make It Safer	2	No: 318, Yes: 142
wat12_	Drinking Water Treatment	6	None: 217, Add Chemicals/Chlorine: 116, Other: 47, Combined Methods: 46, Boil: 17, Filter: 17
wat12_1	Drinking Water Treatment: Boil	2	No: 407, Yes: 53
wat12_2	Drinking Water Treatment: Filter	2	No: 414, Yes: 46
wat12_3	Drinking Water Treatment: Add Chemicals Chlorine Tablets	2	No: 318, Yes: 142
wat12_4	Drinking Water Treatment: Other Specify	2	No: 411, Yes: 49
wat12_5	Drinking Water Treatment: Do Nothing	2	No: 243, Yes: 217
wat12_6_	Drinking Water Treatment: Multiple Methods	2	No: 414, Yes: 46
wat14_3	Other	66	1 Drum: 175, 0: 84, 2 Drums: 58, No: 19, 3 Drums: 17, Drum: 7, Drum-2: 7, Drum 1: 7, 4 Drums: 5, Drum-1: 5, N/A: 5, Drum 2: 4, 2 Drum: 3, 5 Drums: 3, Na: 3, 1 Jug: 2, 2 Drums And 2 Jars: 2, 2 Jugs: 2, 5 Bottles: 2, One Dram: 2, Two Drum: 2, Two Drums: 2, 1 Bowl: 1, 1 Bowl, 1 Drum: 1, 1 Container And 1 Drum: 1, 1 Drams: 1, 1 Drum And 1 Jar: 1, 1 Drum And 2 Jar: 1, 1 Drum And 2 Tanks: 1, 1 Drum And 4 Tanks: 1, 1 Drum And Several Bottle: 1, 1 Drum Set: 1, 1 Drum, 2 Jugs: 1, 1 Jug And 4 Bottles: 1, 1 Little Tank: 1, 1 Water Jar: 1, 1 Water Tank: 1, 2 Dram, 2 Water Pot: 1, 2 Drams: 1, 2 Druns: 1, 2 Jar And 1 Drum: 1, 3 Bottles And 1 Container: 1, 3 Drams: 1, 3 Jugs: 1, 4 Bottles: 1, 4 Jars: 1, 4 Tank: 1, 4 Water Jar: 1, Berl 2: 1, Big Jars: 1
wat15_	Fewest Water Problems	5	Multiple Months: 449, Jul: 5, Apr: 4, Mar: 1, May: 1
wat15_1	Fewest Water Problems: January	2	No: 417, Yes: 43
wat15_2	Fewest Water Problems: February	2	No: 412, Yes: 48
wat15_3	Fewest Water Problems: March	2	No: 330, Yes: 130
wat15_4	Fewest Water Problems: April	2	No: 317, Yes: 143
wat15_5	Fewest Water Problems: May	2	No: 355, Yes: 105
wat15_6	Fewest Water Problems: June	2	Yes: 234, No: 226
wat15_7	Fewest Water Problems: July	2	Yes: 314, No: 146
wat15_8	Fewest Water Problems: August	2	Yes: 248, No: 212
wat15_9	Fewest Water Problems: September	2	No: 303, Yes: 157
wat15_10	Fewest Water Problems: October	2	No: 391, Yes: 69
wat15_11	Fewest Water Problems: November	2	No: 407, Yes: 53
wat15_12	Fewest Water Problems: December	2	No: 410, Yes: 50
liv1_	Income Generation	8	None Of The Above: 281, Other: 69, Multiple Activities: 46, Crop Production: 22, Livestock Production: 19, Betel Nut/Leaf Production: 14, Sea Fishing: 7, Salt Production: 2
liv1_1	Income Generation: Crop Production	2	No: 415, Yes: 45
liv1_2	Income Generation: Betel Nut Leaf Production	2	No: 426, Yes: 34
liv1_3	Income Generation: Livestock Production	2	No: 419, Yes: 41
liv1_4	Income Generation: Salt Production	2	No: 456, Yes: 4
liv1_5	Income Generation: Sea Fishing	2	No: 450, Yes: 10
liv1_6	Income Generation: Aquaculture Shrimp Farming	1	No: 460
liv1_7	Income Generation: Aquaculture Fish Farming	1	No: 460
liv1_8	Income Generation: Other	2	No: 371, Yes: 89
liv1_9	Income Generation: None Of Above	2	Yes: 293, No: 167
liv1_10_	Income Generation: Multiple Activities	2	No: 414, Yes: 46
liv2	Do You Own Businesses	3	NA: 246, Yes I Own The Businesses: 112, No I Work For Somebody El: 102
liv3	Income Generation: Primary Water Source	11	Na: 128, Other: 125, Stand Pipe: 75, Piped Water To Dwelling: 50, Borehole/Tubewell: 42, Surface Water Pond River : 17, Other Person: 9, Protected Dug Well: 7, Rainwater Collection: 5, Small Water Vendor: 1, Unprotected Dug Well: 1
liv4_	Income Generation: Problematic Water Quantity or Quality	4	No: 251, Quantity: 111, Both Quantity & Quality: 55, Quality: 43
pay1	Important To Pay To Install Your Own Water System	4	Very Important: 278, Not Important At All: 108, Somewhat Important: 65, Neither Imp Nor Unimporta: 9
pay2	Important To Pay For Regular Costs	4	Very Important: 172, Not Important At All: 155, Somewhat Important: 126, Neither Imp Nor Unimporta: 7
pay3	Fee Structure For Use Of Main System	7	Free: 344, Fixed: Per Month: 48, Variable: Set Factors: 27, No Fixed Payment: 22, Variable: Per Amount: 11, Fixed Cost Per Amount : 6, Dont Know: 2
wb1	How Do You Feel About Your Life	4	Somewhat Satisfied: 247, Somewhat Dissatisfied: 138, Very Dissatisfied: 47, Very Satisfied: 28
wb2	Change Would Make Biggest Positive Difference	5	Safe Water Services: 353, Better Health Services: 49, Safe Return To Myanmar: 21, Access To Education: 20, Other: 17
wb3	General Health	5	Fair: 199, Good: 124, Very Good: 62, Excellent: 59, Poor: 16
wb7	Feel Unable to Control Important Things	4	Fairly Often: 288, Almost Never: 89, Never: 47, Very Often: 36
wb8	Feel Confident in Ability to Control Problems	4	Fairly Often: 294, Almost Never: 88, Very Often: 53, Never: 25
wb9	Feel Things Going Your Way	4	Fairly Often: 291, Almost Never: 84, Never: 50, Very Often: 35
wb10	Feel Difficulties Could Not Be Overcome	4	Fairly Often: 275, Almost Never: 108, Very Often: 45, Never: 32

Grouped Statistics

The optional grouping variable can be applied to statistics of either variable class. Below demonstrates numeric statistics grouped using the ‘location’ variable:

# create all summary stats
group_df <- text_data %>%
      mutate(across(which(sapply(.,class)!="numeric"),factor)) %>%
      select(-c("gps-longitude","gps-latitude",sd11_3,w8,wat8,wat16,wat17,liv5,liv6)) %>%
      group_by_at("location") %>%
      my_skim %>%
      focus(c(
        skim_variable,
        all_of("location"),
        n_missing,
        numeric.mean,
        numeric.p0,
        numeric.p50,
        numeric.p100,
        numeric.sd,
        factor.n_unique,
        factor.top_counts))

# rename columns
group_df <- data.frame(group_df) %>%
  rename("Type" = "skim_type",
         "Variable" = "skim_variable",
         "Location" = "location",
         "Missing" = "n_missing",
         "Mean" = "numeric.mean",
         "Min" = "numeric.p0",
         "Median" = "numeric.p50",
         "Max" = "numeric.p100",
         "Standard Deviation" = "numeric.sd",
         "Category Levels" = "factor.n_unique",
         "Category Level Counts" = "factor.top_counts")

# round numeric data
group_df <- group_df %>% mutate_if(is.numeric, round, 1)

# group by location
sum_group <- group_df%>%
  filter(Type == "numeric") %>%
  select(-c(`Category Levels`,`Category Level Counts`, `Type`))%>%
  mutate(Question = sapply(Variable, function(x) q_data[[x]])) %>% # add question text column
  relocate(Question, .after = Variable)

kable(sum_group) %>%
  kable_styling("striped", "condensed", full_width = FALSE) %>%
  scroll_box(width = "100%", height = "400px")

Variable	Question	Location	Mean	Min	Median	Max	Standard Deviation
sd3	Age	Camp	38.1	17	36	76	12.2
sd3	Age	Host	38.7	18	37	80	11.1
sd6_	Household: Children Under 18	Camp	3.2	0	3	11	1.8
sd6_	Household: Children Under 18	Host	3.2	0	3	10	1.8
sd7	Household: Adult Members	Camp	2.9	1	2	14	1.5
sd7	Household: Adult Members	Host	3.0	0	2	12	1.7
sd8	Household: Elderly Members	Camp	1.1	0	1	6	1.0
sd8	Household: Elderly Members	Host	1.1	0	1	10	1.1
sd14	House: No. of Rooms	Camp	2.4	1	2	8	0.8
sd14	House: No. of Rooms	Host	2.6	1	2	12	1.1
sd21	Rate Community: Socioeconomic Standing	Camp	2.4	1	2	8	1.2
sd21	Rate Community: Socioeconomic Standing	Host	3.1	0	3	8	1.6
sd22	Rate Community: Water Situation	Camp	2.5	1	2	7	1.2
sd22	Rate Community: Water Situation	Host	3.5	0	3	9	2.0
hw	Calculated HWISE Score	Camp	22.2	0	22	39	7.9
hw	Calculated HWISE Score	Host	15.9	0	16	36	10.2
wat4	Drinking Water: Time to Source (mins)	Camp	18.9	0	10	120	18.3
wat4	Drinking Water: Time to Source (mins)	Host	11.8	0	5	120	15.4
wat5	Drinking Water: No.of Trips	Camp	2.1	0	2	20	2.5
wat5	Drinking Water: No.of Trips	Host	3.1	0	3	23	2.8
wat5a	Drinking Water: Collection Time Per Week (mins)	Camp	348.4	0	140	8400	833.7
wat5a	Drinking Water: Collection Time Per Week (mins)	Host	244.3	0	140	1680	302.0
wat9	Non-drinking Water: Time to Source (mins)	Camp	18.4	0	10	90	16.8
wat9	Non-drinking Water: Time to Source (mins)	Host	12.9	0	5	135	18.2
wat10	Non-drinking Water: No.of Trips	Camp	2.5	0	2	20	2.5
wat10	Non-drinking Water: No.of Trips	Host	3.5	0	3	20	2.6
wat14	Kolosh	Camp	3.1	0	3	9	1.6
wat14	Kolosh	Host	2.3	0	2	10	1.5
wat14_1	Buckets	Camp	3.2	0	3	11	1.8
wat14_1	Buckets	Host	2.7	0	2	10	1.6
wat14_2	Jerry Cans	Camp	1.6	0	1	10	2.0
wat14_2	Jerry Cans	Host	1.0	0	0	15	1.7
wb4	No. Days Poor Physical Health	Camp	4.8	0	4	60	5.5
wb4	No. Days Poor Physical Health	Host	4.5	0	4	30	4.0
wb5	No. Days Poor Mental Health	Camp	4.1	0	2	25	5.0
wb5	No. Days Poor Mental Health	Host	4.1	0	3	30	4.6
wb6	No. Days Health Prevented Normal Activities	Camp	3.0	0	2	30	3.7
wb6	No. Days Health Prevented Normal Activities	Host	3.2	0	2	30	4.0

References

Chang, W. et al. (2022) Shiny: Web application framework for r [online]. Available from: https://CRAN.R-project.org/package=shiny.

Cheng, J., Karambelkar, B. and Xie, Y. (2022) Leaflet: Create interactive web maps with the JavaScript ’leaflet’ library [online]. Available from: https://CRAN.R-project.org/package=leaflet.

Demin, G. (2021) Excel.link: Convenient Data Exchange with Microsoft Excel [online]. Available from: https://CRAN.R-project.org/package=excel.link.

ISCG (2022) Bangladesh - Outline of camps of Rohingya refugees in Cox’s Bazar - Humanitarian Data Exchange [online]. Available from: https://data.humdata.org/dataset/outline-of-camps-sites-of-rohingya-refugees-in-cox-s-bazar-bangladesh.

OCHA (2020) Bangladesh - Subnational Administrative Boundaries - Humanitarian Data Exchange [online]. Available from:https://data.humdata.org/dataset/cod-ab-bgd .

OCHA (2022) WASH Infrastructures GPS Dataset & Map (latrine, bathing, tube-well)_may_31_2022 [online]. Available from: https://www.humanitarianresponse.info/en/operations/bangladesh/document/wash-infrastructures-gps-dataset-ltbathingtwmarch312022.

Sievert, C. (2020) Interactive web-based data visualization with r, plotly, and shiny [online]. Chapman; Hall/CRC. Available from: https://plotly-r.com.

Waring, E., Quinn, M., McNamara, A., Arino de la Rubia, E., Zhu, H. and Ellis, S. (2022) Skimr: Compact and flexible summaries of data [online]. Available from: https://CRAN.R-project.org/package=skimr.

Wickham, H. (2016) ggplot2: Elegant graphics for data analysis [online]. Springer-Verlag New York. Available from: https://ggplot2.tidyverse.org.

Wickham, H. (2022) Stringr: Simple, consistent wrappers for common string operations [online]. Available from: https://CRAN.R-project.org/package=stringr.

hwiseVis

A data visualisation web application created in the R programming language

Rob Taylor

Introduction

Application Summary

Data, Cleansing & Processing

Data

Sociodemographic

Work

Perceptions

Water Insecurity

Access & Utility

Livelihood

Paying for Water

Wellbeing

Cleansing & Processing

Raw Data

Numeric Data

Question Data

Text Data

Mapping

Graphing

Bar Plot

Box Plot

Scatter Plot

Summary Statistics

Numeric Data

Categorical Data

Grouped Statistics

References